How to check if Account exists and create if needed, otherwise lock on to existing record?

[Jason808]

What would be the best way to run an expression that checks the recently added transaction log record and checks against a different table if it has the appropriate accounts and creates it if necessary? I have it almost working from a form submission (works when it needs to create the account, still pops up the form when it doesn't, but the fields are blank). I probably need it to work from an automation. The step does not show this action as an available option probably because an incompatible "do this" type.

Create Or Update AccountTarget

[jballester · Accepted solution]

Hi, 

You can test the following process:

We have two tables:
InventoryLog[ID]
ItemMinMaxLocation[ID]
One action:
create a record in table ItemMinMaxLocation[ID]
One condition:
two values cannot exist simultaneously in the same item of ItemMinMaxLocation[ID]

PROCESS:
Every time an item is registered in inventorylog (event) we want it to check the table itemminmaxlocation[id] (condition) and if the condition is met, create a record (action) itemminmaxlocation[id] with data from inventorylog[log]

1º ACTION We create the action (copyrecord)
- for a record of this table: InventoryLog
- do this: Data: add a new row to another table using values from this row
- table to add to: ItemMinMaxLocation
- set these columns: select

2º - BOT
Event: New record table inventorylog[ID]
Condition:

COUNT(
    SELECT(
       ItemMinMaxLocation[ID],
      AND(
        [Location ID]=[_THISROW].[Location ID], 
        [Item ID]=[_THISROW].[Item ID]
     )
   )
)=0

Process
- Task: Run a data action -> copyrecord

BOT - triggers - conditions - tasks - actions

[Jason808]

Thank you! I can't wait to try it, and hope to remember to add a delay for the bot to when [From Location ID] (moving away from a single location ID in InventoryLog) is not null. Also the next step is to create two updates of ItemMinMaxLocation/Quantity. 1. where the item and From location matches, to decrease the amount. 2. where the item and To location. matches, to increase the amount.

[Jason808]

Thank you. I gave it a try and having some trouble. The action works to create the record by itself but is not being kicked off by the automation. I am wondering if the automation condition is being checked too early?

I tried it with = 0 and with <> 0 and the action doesn't seem to fire either way.

The InventoryLog is being created mostly empty initially and then further in the grouped action, it uses INPUT to prompt the user, then it updates the new record with the correct amounts. I put a step on the bot to delay the check until from location is not null.

Bot: Trigger is InventoryLog record added. Condition: 

COUNT(
  SELECT(
    ItemMinMaxLocation[ID],
    AND(
      [Location ID] = [_THISROW].[From Location ID],
      [Item ID] = [_THISROW].[Item ID]
    )
  )
) = 0

Process Step 1: Wait for From Location is not blank (ISNOTBLANK([From Location ID]))
Process Step 2: Action you posted. Setting Item ID and Location ID (From 'from location id') and the required min and max columns to "".

Any thoughts?

[Steve]

To make sure it's absolutely clear, a bot cannot navigate the user to another view, so no App: go to ... actions using LINKTO...(). The best you can do with the bot is update an existing row or add a missing row to the table using Data: update  an existing row ... or Data: add a row ..., respectively. There is no way to allow user interaction (e.g., using a form) from a bot.

[Jason808]

Thanks for that info. My current process following jballester only has the bot adding a row, conditionally,  but it isn't working. The action works but the bot doesn't seem to call the action. 

[jballester]

Hi, 
Triggering a process could be inconsistent, It works for me in the tests, but not for you.

I have reviewed the documentation for database events: "Data is added, modified, or deleted in one or more columns in an AppSheet database" https://support.google.com/appsheet/answer/11445188#h_a18801a59c

The condition could be inconsistent by not explicitly stating the add, update, or delete of one or more columns.

We will reformulate the process.
1º - for the trigger we will use a condition based on a field, not a record.
2º- for the process we will create a branch process conditional.

Field-based condition
We create a field in the "InventoryLog" table that captures the date each time the record is edited. 
Field -> Name [Modified], type "DateTime", Auto Compute Initial =Now(), and Reset on edit Ok

BOT
Event table "InventoryLog"
Condition: Add and Update

[_thisrow_before].[modified]<>[_thisrow_after].[modified]

Process
We create a process of type "Branch on a condition" with the following condition

COUNT(
    SELECT(
       vehicles in out [ID],
      AND(
        [ID2]=[_THISROW].[ID2], 
        [ID3]=[_THISROW].[ID3]
     )
   )
)=0

We assign the Action to Yes.

[Jason808]

Thank you for that. I may have a use for it one day. I found a way to review the bot actions and found the reason for the failure. Initially I wanted to prompt for a user to enter some of the required fields but this pivot took that away and I didn't realize it or get any errors/warnings (except deep in the bot action monitor). I was able to create two bots to detect the lack of the record and thus create them. I changed the data structure a bit and created a table just for account/item/quantity so no need to worry about updating the min max via form since unable with the bot.