Compare two tables and update non existent records

[Cute_Co]

What are you trying to achieve?

Hello guys, newbie here. Learning in slow motion (and burning my monthly operations in a day )

The final goal is to get values from A (etsy listing_id) and check value in B (gsheets listings.listing_id) and in case the listing_id does not exists on table B, then create them.

Steps taken so far

I’ve created a gsheet with some listings (50 out of 100).
But now I’d like to add the missing ones.

So, in my ignorant belief, I read the gsheets records, then read the etsy records, and create a gsheet records filtering with etsy.listing_id not equal to gsheet.listing_id.

Right? wrong. :(. It goes on and creates duplicated records up to the infinite.
I’ve seen some videos and read some posts about agregators and iterators, but cant really get that to work.

Some helpful soul that can point me in the right direction?

Screenshots: scenario setup, module configuration, errors

[Benjamin_from_Make]

Hello!!!
Welcome to the Make community!

The best way to achieve this is to:

• Do the Search rows as you already did
• Use an Array Aggregator to group all IDs from Google Sheets into one single Array with your IDs
• Do a Etsy/List Shop Listings as you did

From this point, you have the array from Google Sheets, and bundles with all IDs in Etsy. If I understood well, you want to check if an ID in Etsy IS NOT in Google Sheet. If it happens, you add it in the Google Sheet; Is it right?

• Add a “Add a Row” as you did and map all columns with fields coming from Etsy, as you seem to have done already.

And, add a filter. You were very close to the right structure of your scenario! it’s just that the filter is a bit harder, since you need to use Make functions. I will try and show you with my own fake data.

This is my Sheet (I have ids and names)

I have ids 1, 2, 3, 4, 5

After the Aggregator I have an array with a field called “id(A)”

I simulate call to Etsy. I get an id and some other fields

Add a row gets data from Etsy

And now the filter

There are different ways to do. What I did here is:

• I create a new array that gets the “id(A)” from my initial array, but where “id(A)=etsyid”
• I get the Length of this new array
• If this length=0, it means that the ID was not found, so I can continue to “Add a Row”

To implement the map function, you need the “raw name” of the field you want to extract and the one you want to compare.

Here is an animated screenshot to show you how I found it (in my case, the visual name is “id(A)”.

As you can see below, it’s a little confusing because in my case, the raw name is “0”. You may have the same thing than me since you aggregate from Google Sheets as well.

And the result of the call. id=10 was added

Give it a try and let me know if you are stuck anywhere

Benjamin

[Matthew_Bromwich]

Thanks - I got this to work well. However, in my particular case I do not have a unique id to work with. In source A (new data) I have names and dates in the same format as in source B (old data). So I can use those in combination to create a UID. Thus far, I have got it to work wiwth names only. The net effect is that in the new data if there is an existing name found (but with a new date) it gets erroneously filtered out. I can cant figure out to alter the setup and fiter to work with two data sources. I tried just duplicating the filter and adding it as an AND to the filter set up with the new variables but then the logic is flawed. In this proposed solution the first filter will always trigger it to be excluded (ie the matched name). However, if I use the OR it does seem to work but honestly I’m confused as to why. Here are the expressions.

“{{length(map(55.array; 2; 2; 40.col4))}}” where 55.array is the aggregated google sheet. and 2 is the raw data source name, and 40.col4 is the comparator from the csv.
OR
“{{length(map(55.array; 0; 0; 40.col2))}}”. in this case the raw name is 0 and in your example also, and the comparator is the date (40.col2)

A note others might find it useful to know that the aggregator can simply collect only the variables you need. The iterator only seems to need to point to a value in the CSV to go through all the rows,.

[Benjamin_from_Make]

@Matthew_Bromwich

{{length(map(55.array; 0; 0; 40.col2))}}

Hello! Welcome to the Make community!

In your case, what you do works because you want to add an existing name if the date is different. Which means that any new date in the CSV is ok to pass through (whether it’s a new name or not). So your filter is correct. (But be careful to select “Numeric operators: Equal to” for the second condition.

One other way would be to use a Text Aggregator instead of an Array Aggregator, and concatenate name and date, and use comma as a row separator. This would generate a list of namedate fields separated by a comma. You can then use the split function to transform it into an array and compare it with the 2 columns (name and date) concatenated as well.

This would look like this:
for this spreadsheet

The text aggregator

It generates this

This is my CSV (smith exists and same date, doe exists but different date, and durand doesn’t exist).

Here is the filter.
It generates an array from the Text generated by the Text Aggregator, and then it searches concatenated namedate from the CSV with the raw column (1) of the new array. If length=0, it was not in the spreadhseet.

Resulting spreadsheet

Normally you don’t need to use the Iterator because Parse CSV already returns you bundles (and not an array).

Cheers

Benjamin

[Matthew_Bromwich]

Amazing. Thank you. You are correct. The extra iterator was unnecessary in this case, and I also updated the formula to be numeric not text (was my error). I removed the extra iterator and all worked well. I left the UID as the two “or” checks - it works fine. You have no idea how long I was struggling with this seemingly simple “compare and add” problem.

[Benjamin_from_Make]

To be honest, we always all struggle the first time we play with filters

Cheers

Benjamin