I have a table of products that has a column ‘AvailableLocations’ (enum reference to another table called ‘Locations’). Many products are available at 3 locations which are all selected in the ‘AvailableLocations’ column when a new product is created.
ie: ProductName = Golf Balls, AvailableLocations = Vancouver, Montreal
My goal is to have a separate row created in another table, Inventory, for each product and location.
ie:
Row 1: ProductName = Golf Balls, AvailableLocation = Vancouver
Row 2: ProductName = Golf Balls, AvailableLocation = Montreal
At this point, I will then have separate inventory views for each location for my staff to record inventory levels.
The issue is that I am having difficulty figuring out how to automate looping through the AvailableLocations to create a separate row for each location.
You could do so, using either webhook with JSON that adds new rows to the Inventory table when a new product is added to the Products table.
Or you could use action type “Add a new row to another table using values from this row” . You could create three of these actions bundled in a group action.
This group action would invoke as a form save event action when a new row is added to the products table.
The “Only if this condition is true” for each of these 3 actions would be
CONTAINS([AvailableLocations],“Location ID_1”) for action that adds row to location 1
CONTAINS([AvailableLocations],“Location ID_2”) for action that adds row to location 2
CONTAINS([AvailableLocations]," Location ID_3") for action that adds row to location 3
Each of these row add actions will execute only if the corresponding location is contained in the [AvailableLocations] column ( assumed to be enumlist with base type as reference that references the Locations table) in the Products table.
Please feel free to revert back just in case you have any more questions.
Alternatively, if there are many rows ( say 4 or more ) to he added , it may not be practical to create multiple row add actions within a group action.
There are some excellent tips by @Steve about adding multiple rows to a table using looping with actions. Please search for those in the Tips and Tricks section.
Instead of creating separate inventory rows for each location, you can keep the current setup with the Locations enum and use reference as its type. That way, each product simply lists all the locations it’s available at. You can then build reports and lices directly from the reference, grouping or filtering by location when needed.
This approach is simpler and easier to maintain, especially if you got other location tyou dont need to change a thing, its scalable solution. It avoids the complexity of automating row expansion and still gives clear per-location reporting views without duplicating data.