Explanation on how to turn my Iterative logic into nesting formula

[Print_Signs_Mor]

I have read many of the topics here and read through the amazing function documentation provided by AppSheet. I am getting stumped when trying to make my iterative logic work with nesting function.

I am creating an inventory management app for a rental company that I would like to be able to update the inventory of individual equipment pieces based on inventory SETS being “reserved”. And keep track of how many available sets exists for each phrase.

Here is some background:

• My equipment corresponds to a particular 1) letter and 2) color. I have many equipments in stock of the same letter and color. For example, my equipment can be a Green H and I can have 2 in stock and 2 currently available.

• A set can have different and same equipment pieces. So a set may be a phrase spelling ‘COME PARTY WITH ME’. So I would need to reserve 2 Green E’s, 2 Green M’s, 1 Green A, etc. when this set is selected. Multiple sets can be reserved at one time (as individual inventory allows)

• For perspective, there are over 1000 “individual equipment” pieces each with corresponding letters/characters, colors, and quantities.

I currently have three tables. One has the Individual inventory (EQUIP TABLE) (Color, letter, and quantity in stock) of each piece of equipment, one has the individual pieces that make up a set and the required quantity of that piece for that set (EQUIP SET CONT TABLE), and the final has the set reservations (THIS TABLE).

I am trying to calculate the value for total quantity of sets in stock for a particular color.
This is the written iterative logic:

FOR EACH LETTER
WHOSE [PART OF PACKAGE] ROW VALUE IN THE EQUIP SET CONT TABLE IS EQUAL TO THE [PACKAGE ID] ROW VALUE IN THIS TABLE,

FIND THE [LETTER] THAT HAS THE SAME [COLOR] ROW VALUE IN THIS TABLE AS THE [COLOR] ROW VALUE IN EQUIP TABLE,

AND IF FOR THAT ROW, THE [TOTAL QUAN] IN THE EQUIP TABLE IS GREATER THAN OR EQUAL TO THE [QUANTITY REQUIRED FOR SET] IN THE EQUIP SET CONT TABLE,

ADD THE ROW VALUE IN THE [QUANTITY REQUIRED FOR SET] IN THE EQUIP SET CONT TABLE TO THE [RESERVED QUANTITY] IN EQUIP TABLE,

RETURN THE NUMBER OF TIMES EVERY [REQUIRED QUANTITY OF LETTERS] IN THE SET CAN BE RESERVED FROM THE [INSTOCK QUANITITY] OF THE EQUIP TABLE.

[Represent Table Columns]

Can someone explain to me how to get started with this?

[WillowMobileSys · Accepted solution]

Print_Signs_More:

I am trying to calculate the value for total quantity of sets in stock for a particular color.
This is the written iterative logic:

I would first recommend to add to your “Equip Table” an [Available Quantity]. So [Available Quantity] = [Instock Quantity] - [Reserved Quantity].

When an Order is being placed, the Form checks ordered quantity against the [Available Quantity]…NOT [Instock Quantity]. If not enough available, then the Form only allows a max of [Available Quantity].

Anytime an Order is saved, an action on Form Saved adjusts the Reserved amount. I would do this with a SUM() that recalculates the [Reserved Quantity] for each Letter/Color included in the Order based on all OPEN Order Items. By re-SUMMING the values, this has the benefit of auto-correcting any values that may have gotten out of sync.

When the Order is sent out, [Instock Quantity] is adjusted AND [Reserved Quantity] is re-calculated. In turn [Available Quantity] is automatically re-adjusted based on the App Formula re-firing upon changes made in the row.