Solved: Copy Multiple rows from One Table to Another - Page 2

blaineiler · 03-12-2023 07:10 PM

Hello,

I am setting up an app to manage production scheduling and management for a food manufacturing company. For simplicity, we have a few main tables for the app, as follows:

Item Master: complete listing of all the inventory items, including both finished goods and the raw materials

Product Formulation: a one to many relationship to Item Master, as the formulation may evolve over time, or we could have multiple versions of the formula based on raw material inputs that can be substituted

Schedule: lists an item, product formulation version, production date, status, planned quantity, etc.

We set a production schedule several weeks out. The day before, our team "stages" the scheduled production. When that happens, we set the status of the Schedule row to "Staged," and then we want to add the rows for the selected Product Formulation in a new table called Schedule Item Formulation. Why? Because from there, we have a warehouse function where we log inventory of each item that is picked, linking it to the Schedule row. At the end of production, we may "return" inventory of each item that is unused. From there, we can calculate the amount of each input on the formulation used, and calculate actual vs expected yield.

In summary, I cannot figure out a way to copy the rows in the Product Formulation table -- specific to the item on the Schedule row -- to the new Schedule Item Formulation table for further data collection and analysis. I know there is a way to copy one row of data from one table to the other, but cannot figure out how to copy multiple rows.

Thanks!

Elvic

I have been solving a lot of similar problems in my app also for a food manufacturing company. Items, Recipes, Production Schedule, Pick Lists, Prep Lists, etc.

As I understood the task is quite simple. You have a list of items on "Schedule". Once the line on "Schedule" gets "Staged" status you want to copy the specific recipe for this particular item from your "Product Formulation" to "Schedule Item Formulation". If I get it correctly, you should do the following.

Create a bot for "Schedule" with an event type "Updates only". Then set a process with type "Run a data action". Select "Run action on rows". Select referenced table as "Product Formulation" and set the filter as:

filter(
"Product Formulation",
([Formulation ID]=[_THISROW_AFTER].[Formulation ID])
)

Create an action for "Product Formulation" which symply copies the lines from "Product Formulation" to "Schedule Item Formulation".

You will also need some reference to Schedule ID in "Schedule Item Formulation" and that was a bit tricky for me but I found out how to do it. Will share with you if all of the above is helpful for you.

View solution in original post