Meta BOM Data Integration and SharePoint Setup -- 2

Budget: ₹1500 - ₹12500

Phase 1: Meta BOM Data & SharePoint Structure This phase focuses on getting the initial meta-Parts data into the system and setting up the foundation. Excel as the Source: Specified that the master BOM data will start in Excel files located in a centralized folder (Ex: C:\Input Data\BOM). This is a crucial step. A Power Automate flow can then be set to trigger whenever a new Excel BOM is added or modified in that specific library. Data Flow to SharePoint: A Power Automate flow will read the new/updated Excel file, identify the header and line items, and populate into three SharePoint lists: BOM Header List, BOM Parts List, Part Master List (for all required data) Bi-directional Sync ("vice versa"): Mentioned that creating a new part in the system should feed back to the centralized Excel BOM. This is achievable. A second Power Automate flow can be triggered when a new item is created in to SharePoint Part Master list. This flow would use the "Excel Online (Business)" connector to find the correct Excel file in the document library and add the new part number as a new row. Phase 2: Release Management Process The workflow is very well-defined and perfectly suited for Power Automate. Here is exactly how it would be built: Initial State: When parts are created from Excel, the flow will set their default status in SharePoint to "Draft". Trigger: The Designer changes the status of an assembly from "Draft" to "Submit for Review." This change triggers the main Power Automate release workflow. Validation (CN Number Check): The very first step in the flow will be to check if the CN Number field for that assembly is filled in. As you stated, releases only happen based on a CN Number. If the field is empty, the flow will stop, change the status back to "Draft," and email the Designer with an error message: "Please assign a CN Number before submitting for release." First Approval (Design Lead): The flow uses the "Start and wait for an approval" action and assigns the task XXXX XXXX Design Lead. The Design Lead receives an email and a Teams notification with "Approve" and "Reject" options. If Rejected, the flow changes the status to "Rework (Design)" and sends a notification back to the Designer with the Design Lead's comments. The workflow ends until the Designer resubmits. If Approved, the flow proceeds to the next step. Second Approval (Project Manager): After Design Lead approval, the flow assigns a new approval task XXXX XXXX Project Manager. If Rejected, the flow changes the status to "Rework (PM)" and notifies the Designer with the Project Manager's comments. If approved, the flow moves to the final step. Final Release Selection (by Designer): Upon Project Manager approval, the flow will send a final email to the original Designer. This email will contain links that, when clicked, set the final release status. This gives the designer control over selecting the flag. The links could be: "Set status to Preproduction (Yellow Flag)", "Set status to Tooling Kick Off (Green Flag)", or "Set status to Production (Dark green Flag)". Once the designer clicks a link, the status is set, the item is locked from further editing, and the workflow is complete. Phase 4: BOM Compare - This feature directly addresses a key user need. Technical Implementation: This functionality requires custom logic that is beyond standard SharePoint web parts. As we discussed previously, this is the perfect use case for a custom SharePoint Framework (SPFx) web part. User Process: The user would open the BOM Compare page, be presented with two dropdown menus (populated from your BOM Header list), and select the two BOMs they want to compare. Backend Logic: The SPFx code would then fetch all the child items for both selected BOMs from the BOM Parts List. It would compare the two lists of child parts to identify additions, removals, and quantity changes, and then display the results in a color-coded table.