Hi all,
I’m syncing data between a Make data store and a SeaTable database (an Airtable clone). I’m trying to identify entries from the Make data store which are not in the SeaTable database, yet.
In my case I’m comparing the entries of the columns “Kundennummer” (customer number) in both Make data store and SeaTable database. Logically the best way would be to list all rows from SeaTable, then search all records from Make. In the “Search records” module for the Make data store I would set a filter for all entries (Make) which are not equal to entries from SeaTable.
As a result, I’m getting 54 of 55 entries, except for the first (‘10000’).
Clear thing, I thought. I just put an iterator between SeaTable and Make. Nearly same result. I’m getting 54 operations whith respectively 54 bundles.
Let’s try an aggregator then. Also a similar result here. I’m getting 54 of 55 entries as an output, except for the first entry.
What am I doing wrong here? How do I set this up right?
Hi,
I would suggest trying something like this:
Aggregate all your data from SeaTable (like you did in your last screenshot), for simplicity, you can choose only to aggregate the Kundennummer field.
Then search your datastore without any filter, so it outputs all the data, once again aggregate the Kundennummer.
Now you would use an iterator, inside the iterator you would combine the output from both aggregators, for example merge(array1;array2).
This way you would iterate through all the Kundennummers (remember to use a deduplicate() function around your merge)
Now after your iterator have a router with 2 filters.
Filter 1: If the number does not exist in our seatable aggregation, then it needs to be added to Seatable
Filter 2: If the number does not exist in our datastore aggregation, then it needs to be added to the Datastore
Your scenario will look something like this:
The array you will iterate will look something like this:
Note that when you are testing don’t use the IML inside the iterator, set a variable to the array, and use the variable inside the iterator. Then after testing you can place the direct IML into the iterator. (Note you may need to do some mapping for each array depending on its structure)
Let me know if this makes sense and works for you.
2 Likes
Hey @IOA_Harman,
first of all, thank you very much for your help! 
I needed some time to test it but still I don’t get the data I want. This is a tough one.
I tried two solutions which look like this.
Solution 1:
I aggregated the data from SeaTable, set a variable to it and aggregated it again to get the customer numbers (Kundennummer) in one array.
It looks like this:
In the following ‘search records’ module for the Make database I set a filter to get the entries which are not equal to the SeaTable database. Unfortunately, I only get the data which is already in the SeaTable database. I can’t get the additional entries of the Make database which I want to transmit to SeaTable.
What am I doing wrong?
Solution 2:
In the second solution I push the data (customer numbers) from SeaTable and Make respectively via the variables in one array.
SeaTable data in variable (53 entries):
Make data store data in variable (55 entries):
In the last variable I merge the data from variable 1 (SeaTable) and variable 2 (Make) together (equals 108 entries). I then deduplicate the merged data to only get the two non-duplicated entries.
As a result I get all 55 entries except of the two non-duplicated two entries.
I can’t see where my mistake is. Do you have any advice for me?
1 Like
Hey @tobimahony
Your solution 2 looks good to me. The last step here would be to iterate this deduplicated array, and in your router filters have logic like this:
Filter 1: We compare if the current number (from our iterator) is part of the array you saved in the variable for Seatable AND it is NOT part of the array you saved in the variable for your datastore.
Filter 2: Similar to filter 1 except this time we check if the number is part of the array you stored for the datastore numbers AND NOT part of the array for the seatable numbers
Note a few things:
-
that in the filters this would be an array contains or array does not contain operator, the filter would look similar to this, changing the mapping for the arrays:
-
you can set multiple variables (so you only need 1 module), set 1 variable to seatable numbersarray, set second variable to datastore numbers array, set third variable to the combined and deduplicated array of numbers.
-
name each variable so it is clear when you use each one (example: seatableNumbers, datastoreNumbers, allNumbers)
3 Likes
Yes, it worked! Thank you very much for your help! A nice little present before christmas. 
Enjoy some quite christmas days, all the best for you. 
3 Likes
