If the data already exists then increase the quantity, if it doesn't exist then add the data

[agnispremz]

Hi everyone. So I have 2 tables, an item list table and an incoming item table with columns called item name and item quantity. I want to create automation that can check whether the name of the item entered in the incoming item form already exists or not in the item list table. If the item name already exists, then the quantity of newly arrived items will be added to the quantity currently available. If the item name does not exist, then add the item name and quantity to the item list table. Is there a way to do this? Thank You

[Hussein_Osseily]

You can Creat a branched condition (yes/no) using something like:
IN([Name_Column], items_table[Name_Column])
General Syntax: 

IN(text-to-search-for, list-to-search)

IN() 

If yes: 
data action
referenced rows , you can select the [name]=[_thisrow].[Name]
and then sum the qty of the incoming + existing 

If no, do a data action that will just write the item in the item's list

[agnispremz]

I finally understand what you mean, but I'm having difficulty creating body for the automation both in adding new items and in adding quantity to existing items. When I test the automation, I always get a 400 Bad Request error, I always get a 400 Bad Request error, can you help me identify the cause of the error. The following is an example of a body that I have made:

Add new item:

{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": [
{
"Item Name": "<<[Item Name]>>",
"Qty": "<<[Qty]>>"
}
]
}

Add item quantity:

{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": [
{
"Item Name": "<<ANY(SELECT(Item List[Item Name], [Item Name]=[Item Name]))>>",
"Qty": "<<ANY(SELECT(Item List[Qty], [Item Name]=[Item Name]))+[Qty]>>"
}
]
}

[Hussein_Osseily]

What are the tables column names ? If you may list down the column names and the table names of which you are doing the transfer , so i can help you with this. Specify as well your key column name of each table

[agnispremz]

The first table name is "Item List" which has columns named ID as Key, Name, and Qty. Second table name is "Incoming Items" which has columns named ID as key, Name, and Qty. Automation will be triggered when data is added to the "Incoming Item" table, then the bot will check whether the added data already exists or not in the "Item List" table. If it already exists, then the Qty of the item with the same name will increase. If it doesn't exist yet, then the added data is also added to the "Item List" table.

[Hussein_Osseily]

The name of the item is always the same ? Or the ID of the item

never mind, i think you mentioned that its by the name , ill check this for you when i login my laptop

[agnispremz]

Firstly, thank you for answering my question. Second, I have succeeded in adding data to the "Item List" table using behavior. However, I still get an error when I want to increase the Qty for incoming items already listed in the "Item List" table.

[Hussein_Osseily]

If you send me your gmail i can add you to the simple app and copy it.
Two tables: 
IncomingItems
Items

In incomingItems you add an item
if it doesnt exist in the Items table it will add the record and delete it from the incomingItems table
If it exists in the items table it will update the QTY and delete it from the incomingItems Table. 

Video Below:
https://drive.google.com/file/d/1GnXw4F4vZN7tCISIy8SJAzQUzG7tqq_P/view?usp=sharing

[agnispremz]

Sorry for the late response, I wanted to ask again first. Can the existing automation work if there are other columns so that the structure of the two tables becomes IDs such as Key, Name, Location, and Qty?

[agnispremz · Accepted solution]

Hi, I think I have found the solution to my own problem. I tried tinkering with the automation code that I created and finally succeeded in making the code run. The solution to the problem I experienced is as follows (ps. sorry if there are some parts that use Indonesian):

When an item arrives and is not yet on the item list, the automation used has the following conditioning

Then, for the task to be executed, namely Run a Data Section, using behavior that has been modified like this:

Finally, if an item arrives and the item is already in the item list, then the current number of items will increase with the conditioning and code in the automation body as follows:

Code on the automation Body:

{
"Action": "Edit",
"Properties": {
"Locale": "en-US",
"Location": "47.623098, -122.330184",
"Timezone": "Pacific Standard Time"
},
"Rows": [
{
"ID Asset": "<<ANY(FILTER("Daf-tar Asset", [_THISROW].[Nama Asset]=[Nama Asset]))>>",
"Lokasi": "<<ANY(SELECT(Daf-tar Asset[Lokasi], [_THISROW].[Nama Asset]=[Nama Asset]))>>",
"Jumlah": "<<ANY(SELECT(Daf-tar Asset[Jumlah], [_THISROW].[Nama Asset]=[Nama Asset]))+[_THISROW].[Jumlah]>>"
}
]
}