How to borrow N units from one table row to another

[Dhiraj_Shetty]

Dear Community Member,

I am creating an Inventory Management App. I have independent inventory holder entries on each row.

Now I want to create a form where we can migrate some units of inventory from one inventory holder to another.

In other words the Units column of type Number must decrease in lender row and increase in borrower row. The number of lended units should be dynamically specifyable as user input.

Forgive me if this is a silly or repeat question.

Regards,
Dhiraj

[WillowMobileSys]

Hi! And welcome to the Community!

Dhiraj_Shetty:

I want to create a form where we can migrate some units of inventory from one inventory holder to another.

In this Migration Form you will need to specify the From and To. I would record these as REF columns to your Inventory rows. REF columns allow to access ANY of the column data for that row. How you assign the referenced rows to the REF columns depends on you app activity. The most basic way is to provide pick lists that allows the user to choose the From and To inventory rows.

But maybe in your use case you have a way to automatically identify one or both of the rows. If so, apply them. For example, maybe you are launching the Migration Form from one of the rows as the From row. If so, then you know that row and can automatically assign it as the From Ref row.

---

Additionally, you will need a set of actions that perform the actual “migration”. This is simply an action(s) that “decrease in the lender row” and another action(s) that will “increase in the borrower row”. These actions are then combined into a Grouped Action so they always run together.

---

With your Form set to assign the From and To Ref rows AND the Grouped action created that perform the migration, then you have two ways to perform the actual migration upon Save of the Migration Form:

1. There is a From Saved behavior on each Form view that allows you to attach custom actions. You would simply attach the Grouped action to this Form Saved Behavior.

2. Use a Bot that is triggered upon the ADD of the migration row.

Which way you activate the Grouped action may depend on your app but many times is just a matter of personal preference. Using the Form Saved Behavior will result in the user seeing the updates immediately in the app on their device. Everyone else would not see them until their next Sync. Using a Bot, no one will see the updates until after a Sync is performed, This can sometimes create confusion of for the user since they may not see the updates for several seconds. For this reason, I prefer using the Form Saved Behavior as my first choice. Be aware it is not always the best option. In your case I think it is.

Here are some articles to help understand some of the things I mentioned:

Different types of references between tables

help.appsheet.com

References Between Tables

Learn about references, when to create them, and how to use them so your data makes sense.

Form Saved option - Look for "Form Saved"

help.appsheet.com

View Events

View actions can run when certain events happen and help you create custom navigation.

Bot option - Check out "Run a Data Action"

help.appsheet.com

Step: The Essentials

[Dhiraj_Shetty]

How do I share the count (number of units to be lended) while Incrementing the number of Units in the Borrower Row?

Its easy to Decrement the number of units in the Lender Row since I am doing the Lend Action on a particular Lender Row. Please confirm if my question makes sense to you.

[WillowMobileSys]

Dhiraj_Shetty:

How do I share the count (number of units to be lended) while Incrementing the number of Units in the Borrower Row?

So, maybe your design was not as I had thought. When you said you wanted to create “a form where we can migrate” I thought you had that already.

What I would do is include another table lets call it Migrations. this table would have at least 4 columns (and maybe more if you want to track other details):

Migration ID - a unique ID for each row
From - a Ref column to the “lending” Inventory row.
To - a Ref column to the "borrowing: Inventory row.
Quantity - the Units to be transferred.

Your migration Form would be against this new Migrations table.

Once the Migration row is save you use the Actions as I described above. These actions would be of the type - “data: add new row to another table using values from this row”. This action type was updated fairly recently so that if used AND you specify a row key that already exists - it will UPDATE the row instead of adding a new row.

CAUTION - there were some Community reports that in the action type mention above, if columns were OMITTED from assignment, those columns would be blanked out - assigned blank. I have not played with the action in this way to determine the expectations and it may have been adjusted by AppSheet. The resolution, if this situation still exists, is to simply re-assign all columns. With a REF column, which you will have, this is easy.

I’m sure you will have questions, so please ask away!

[Dhiraj_Shetty]

Hello John,

Thanks for your solution. I will get back to you on this.

I am currently looking at the requirement of having the Transfer action available on the detail view of any inventory item. I have hence created an independent Transfer Action applicable on any record of Inventory Table. I added two Ref Columns, TransferFrom and TransferTo to this Inventory Table.

When I transfer from RowA to RowB, the end result should be:
RowA.TransferTo → RowB
RowB.TransferFrom → RowA.

On RowA Detail view, when I click on the transfer Action, I can modify its “TransferTo” field easily. But how do I modify the “TransferFrom” of RowB in the same action (Group).

If you think I am digressing from the topic, I will post this as an independent topic.

[WillowMobileSys · Accepted solution]

Dhiraj_Shetty:

When I transfer from RowA to RowB, the end result should be:
RowA.TransferTo → RowB
RowB.TransferFrom → RowA.

Got it! Somehow you need to specify the # of Units to transfer - unless you are always transferring ALL of the Units.

This is a way to do it IF you do not require keeping track of the history of the transfers. When you perform a second transfer, you would have to overwrite the first and then any knowledge of the first transfer would be gone (unless of course you are logging the activity after the fact).

Dhiraj_Shetty:

But how do I modify the “TransferFrom” of RowB in the same action (Group).

You cannot do it in the SAME action. You will need to create two separate actions - one for RowA adjustment and a second for RowB adjustment. Then a third action would be a Grouped Action that lists both of them to execute together.

In your design, your RowB adjustment would still use the action type of: “data: add new row to another table using values from this row”. You would set the [Row Key Column] equal to:

[Row A].[TransferTo].[Row Key Column]

This will use the Key from RowB and since the row will already exist - perform an UPDATE instead of an add. Then in Set Columns, assign all the other columns, if still need to do to prevent overwriting as mentioned above, and you can use a similar expression for each one:

[Column Name] = [Row A].[TransferTo].[Column Name]

NOTE: You would NOT explicitly use the “=” when inserting the columns into the action. Instead, set the target column on the left-hand side to [Column Name]. Then apply the expression on the right-hand side.

For your Units adjustment, you would use the expression to modify the RowB Units. The below expression assumes a user specified amount. Otherwise, if ALL Units then use RowA’s Units column.

[Units] = [Row A].[TransferTo].[Units] + [_THISROW].[Transfer Units]

I hope its becoming more clear!?!?