I am writing an inventory management app.
if the user created a transaction by clicking โsaveโ in the form โTransactionโ. The system should do an auto action to save this transaction entry in the โTransactionโ table. At the same time, the system should do an โActionโ to update the total quantity of that specific product in another table.
In this โActionโ, i want to search if there is already a row in the table for that product. If there is, the system should change the quantity accordingly. If there isnโt, the system should create a new row. Can appsheet do this?
Thx!
Hi @hungpuiki
Yes
You may want to use a virtual column to calculate it.
Did you have a look on this sample app ? The mechanism may inspire you.
https://www.appsheet.com/templates/Update-inventory-and-monitor-levels?appGuidString=722f8a69-cdbe-4...
Thx for the comment. But this sample is not useful for me. In this sample app, the user can add a virtual column to each product to calculate the total quantity of the product.
However, in my particular case, I am handling a matrix of [Product] x [Location]. It cannot be done by the same way with what the sample app did.
OK, I read your post too fast, my mistake.
@Suvrutt_Gurjar 'as answer seems to fit to your expectation.
If you wish to do it with real columns, you will need to use a combination of a few actions. This would be a bit extended, complex implementation including actions and conditions to fire those actions.
Thx for your comment. I tried but I canโt.
What I need is a condition, i.e. if (a row can be searched), then (update the row), else (create a new row). Group of sequential actions means both actions have to be executed under whatever condition.
" Data: Execute an action on a set of rows " cannot take a value that I input to a form as a variable for calculation. Because an action is independent to any forms and what you have just input to these forms.
I believe it is possible. I tested it on a small test app before responding. As mentioned the conditions will be a bit complex.
This is not correct. You can set different conditions for constituent actions in the group action. Those constituent actions will execute depending on the respective conditions of the constituent actions.
Not sure what you mean by this. Please elaborate. Please note that the value from the form will be used after the form is saved because group action is to be set as a form save event action.
Can you advise me how to add a condition a group of actions? Do you mean you want to do it like: [Available qty] = if (the row exists), then (change the [Available qty]), else (return). But even if we do that, the next action (create a new row) will be done anyway.
Sorry if I may be wrong. so do you mean in my particular case, the action will search the table of โAvailable locationsโ by using the [location] and [productID] in the last row of โTransactionโ table? Is there any expressions that we can use to achieve this?
A) The Parent action in the โTransactionsโ table the condition
IN([_THISROW].[Product ID] ,Available Locations[Product ID])
B) The reference action on the โAvailable locationsโ table the condition can be
[Key Column of Available Locations]<>MAXROW(โAvailable Locationsโ,"_ROWNUMBER")
NOT (IN([_THISROW].[Product ID],Available Locations[Product ID]))
Edit: There was type in action condition in point 2 above. Corrected as highlighted.
Thx for enlightening me the concept of reference action! You are really experienced in appsheet!
However, I encounter another problem. You may say it as a bug. I am doing a testing based on your suggestion. Therefore, setting the group of actions as the โEvent actionsโ when I click save.
However, the โAvailable locationsโ table cannot be updated. Instead, if I execute this action by clicking the action button, the correct row of โAvailable locationsโ is updated.
It seems that the idea of โthis rowโ cannot be recognized by appsheet in a form view for you have just saved!
If you show the screenshots of the action, one may suggest.
To simplify the problem, I didnโt use your expressions. I use the most orthodox approach, i.e. setting up relationship between the 2 tables to select the list of rows. And condition to execute this action is always true.
Thank you for nice screenshots. Somehow action conditions are not captured. Anyway presume your action conditions are simply โtrueโ for now.
The child table โAvailable Locationsโ detail view is showing Location&ProductID and Product_ID values with yellow triangles. That suggests their referencing of child records with parent records is broken. Please take a look at the โYellow Triangleโ related issue posts and you may want to set it right.
https://community.appsheet.com/search?expanded=true&q=yellow%20triangle%20%23ask
Many thx for pointing out my problem! You are a true master!
The problem has been solved now. The problem is with โyellow triangleโ (broken reference).
Therefore, I gave up using relationship to relate the row that I want to select in the child table. I use filter() instead. Even though the problem has been solved, for sharing to those who may see this post in the future, I set out below the details in my implementation for your reference.
Parent action:
Name: Change available qty (Parent)
Reference row: filter(โAvailable locationsโ,AND([Location]=[_thisrow].[Location],[Product_ID]=[_thisrow].[Product_ID]))
Referenced action: Change available qty (Child)
Condition: IN([_THISROW].[Location&ProductID],Available Locations[Location&ProductID])
Child action:
Name: Change available qty (Child)
Do this: Data: set the value of some columns in this row
Set these columns: Available qty = [Available qty]+sum(select(Transactions[Qty_change_IO],[Transaction_key]=MAXROW(โTransactionsโ,"_ROWNUMBER")))
Condition: True
Group actions:
Name: Group of available locations actions
Actions: (1) Change available qty (Parent); (2) Create new row in available location
Condition: True
Create new row:
Name: Create new row in available location
Do this: Data: add a new row to another table using values from this row
Set these columns: (1) Product_ID = Product_ID,โฆ
Condition: NOT(IN([_THISROW].[Location&ProductID],Available Locations[Location&ProductID]))
Good. Thank you for the detailed update. Are the rows getting added now as you desire?
Yes!
Good. However, I think you will need some fine-tuning of action conditions on more testing. There may be some edge cases when the action conditions will possibly not work and as such may need finetuning.
Thx for advice! i will run a few test cases after the framework is done.
User | Count |
---|---|
41 | |
31 | |
31 | |
16 | |
14 |