Copy Multiple rows from One Table to Another

[blaineiler]

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!

[WillowMobileSys]

You will likely need to use an action of type "Execute an action on a set of rows".  In that action you provide an expression that will select one or more rows to act on and "calls" a referenced action on the SAME table that then "Adds a row to another table" - which I assume you already know about.  See image below.

If you have questions, please ask.

Action to select rows to be copied and send to a Referenced Action to perform the copy

[blaineiler]

Thanks for responding. I'm not sure if this works. I want to be able to call the action from the schedule row. The schedule row references a formulation. When I reference the formulation table, it can't be called from the schedule row. Any thoughts?

[Elvic]

When In the schedule row changes are made this triggers a bot. Bot is
using the data from Schedule row which triggered it. You have an ID of
formulation there. Bot triggers a process to apply an action on a
number of rows in Formulations. You filter the condition to select
only the rows with the formulation you need using the data from
Schedule row which triggered the bot. An the action that is selected
for Formulations then copies the filtered rows of the formulation you
need into Scheduled Formulations table. Should work or I missed the
point

Sent from my iPhone

[blaineiler]

Sorry, I was replying to what @WillowMobileSys posted. I have not tried your method. With the bot triggering on updates, I need to think of a filter to lock it down. The schedule has a status field, so one way would be to make it fire when the status moves from "scheduled" to "staged." But if for some reason the work order gets "un-staged," I would need to find a way to delete the rows that were already copied to avoid duplication.

[Elvic · Accepted solution]

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.

[blaineiler]

I think this could work except for the issue I mentioned above. Although I think I could probably create another bot that triggers based on the before value of "staged" and the after value of "scheduled." Basically that would track "un-staged" production.

I'm getting an error though on the filter formula: Unable to find column 'Formulation ID'.

[Elvic]

The issue you mentioned above is when you need to unstage the work order solved by creating another bot looking at "Staged" becomes "Unstaged" which triggers deleting the rows from Sheduling with an ID from "Unstaged" work order.

"Unable to find column 'Formulation ID' " - might be the issue related to the different column name you have in "Product Formulations"

[blaineiler]

Yes, that's right. I made that tweak and it works. However, I also need to paste the ref value for Schedule ID in the newly created rows in Schedule Item Formulation. That way I can see the ref rows for the formulation from the schedule record.

[Elvic]

I am not sure that the way I do it is the right one. I found it triky to pass the value from "Schedule" into "Schedule Item Formulation" since "Schedule Item Formulation" is not updated from the bot directly but from the related Action where we copy lines from "Product Formulation" into "Schedule Item formulation". 

The walkaround I used is that first I updated the temporary column on Product Formulation. I saved there an ID from "Schedule". Then I let the Action do its job by copying everything including that temporary "Schedule ID" column and the final step (under the bot) is erasing the temporary column in Product Formulation. 

If you find another more sophisticated way of doing the same please let me know)

[blaineiler]

This seems like it should work just fine. Can you share how you set the temporary Schedule ID in the Formulation table? From there, it seems easy enough to have that copy over with the other fields. Also, would love to see how you are setting up the deletion bot.

[vsingh]

I have 2 tables one is inventory & 2nd is purchase & i want to copy item code & item name from purchase table if same item is not available in inventory table.