I am working on a model driven app. I am trying to create an excel or word document with a BOM or parts list. I need to calculate the number of individual components for a job, and send that as meaningful data to Procurement. This needs to be at least a semi automated process. I know this can be done, but I haven't stumbled across a solution on the net yet which just means I am searching the wrong terms most likely. I have tried document templates, and flow but have not yet cracked this yet.
Can anyone point me to relevant recourses that may assist setting this up. I am able to extract the values in Power Automate using the expand query, which will list the results, but I need to count how many of each component is used as they can be referenced multiple times or are required multiple times per assembly.
Expand query seems to work but it will need adjustment anytime our quote table changes ie we add additional lookup columns if/when needed. As it stands in this configuration Table 3 has 2 lookup columns, each lookup column references 15 or so values in Table 2.
I have three tables that are connected via lookup columns in Dataverse.
Table 3: Quote table (choose assembled parts from table 2)
Multiple lookup columns (1:n) Relationship to
Table 2: Assembled parts sold to end customers. (Choose assembled parts from Parts Catalog)
Multiple lookup columns (1:n) Relationship to
Table 1: Parts catalog of all individual components.
***Secondary for reference*** Once I have these values, I intend to do two things. One is export them to excel, word, or etc for procurement to order (Create a BOM) I also intend to filter the results to certain referenced part types to add to another table for weekly health check and reporting for the equipment after it is installed.
