Dataverse, lookup related values then calculate number of items, from those related tables.

[Ozman_15]

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. 

[EricRegnier]

Hi @Ozman_15,

We might need more details on what type of count/calculation you need, is it to count the total of parts on quote, group the count by part type, etc?

But you can easiler count the part on Quote table with a rollup fields/columns on Table2 that counts the parts. More info on rollup fields: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/define-rollup-fields

Then with Power Automate you can create the desired doc type (CSV, Word, Excel), format it and save it to a location taht procurement has access (for example SharePoint). Here more info on CSV and PowerAutomate: https://powerusers.microsoft.com/t5/Power-Automate-Community-Blog/Export-to-CSV-Using-Power-Automate... 

Hope this helps!

[Ozman_15]

Thank You for the information and taking a few minutes to help out. I am looking into rollup fields now. I was actually unaware that these existed.

Currently Table 3 lets me choose two items from Table 2, since this is still proof of concept. Once POC is complete then it will probably expand to Ten and may even expand further.

Table 2 can reference the same part from table one multiple times. Part "A" might be referenced 5 times and part "B" 3 times in each row. Part "A" and Part "B" are further referenced in the same manner in other rows in table 2.

Table 1 will be a full catalog of parts and will be large once this is in service.