How to create condition on an action?

hungpuiki
Participant II
  1. I am writing an inventory management app.

  2. 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.

  3. 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!

0 17 1,326
17 REPLIES 17

Aurelien
Participant V

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...

@Aurelien

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.

  1. For updating [Avialable Qty], use a reference action of type " Data: Execute an action on a set of rows" on the “Transactions” table that updates the related row of an existing product in the “Available Locations” table

https://www.appsheet.com/templates/This-app-shows-how-to-use-reference-actions?appGuidString=e76d2e7...

  1. For creating a new row in the “Available Location Table” , use an action of type “Data: add a new row to another table using values from this row” on the “Transactions” table
  1. Then group actions described in 1) and 2) above in group action type “Grouped: execute a sequence of actions” and set this group action as form save event action on Transactions table form save.

@Suvrutt_Gurjar

Thx for your comment. I tried but I can’t.

  1. 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.

  2. " 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.

@Suvrutt_Gurjar

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?

  1. For updating rows, for " Data: Execute an action on a set of rows " the condition can be something like

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")

  1. For creating a new row in the “Available Location Table” , for an action of type “Data: add a new row to another table using values from this row” , the condition can be something like

NOT (IN([_THISROW].[Product ID],Available Locations[Product ID]))

Edit: There was type in action condition in point 2 above. Corrected as highlighted.

@Suvrutt_Gurjar

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.

@Suvrutt_Gurjar

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

@Suvrutt_Gurjar

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?

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.

@Suvrutt_Gurjar

Thx for advice! i will run a few test cases after the framework is done.

Top Labels in this Space