Move records to a different view based on a new update

[Scott_Heliker]

I have car inventory that gets updated daily through Integromat. I need to overwrite the entire table with the new CSV that gets pushed in from Integromat daily and have it compare the new entries being brought in with the current entries. If they both match, it will just keep that record meaning the car is still available and won’t make a duplicate record. Any cars that don’t match would mean they are sold so need to move those off the live inventory table view to the sold view.

How can I accomplish this?

[W_Vann_Hall]

At first, I thought the CSV Import Block was exactly what you needed – but then I read a little deeper.

One possibility would be to make your process something like this:

1. Define three tables: [NewList], [PastList], and [Match].
   • The first two have the data structure you import from Integromat.
   • [Match] has a single record.
   • Every record in [NewList] and every record in [OldList] link to the single record in [Match].
   • [Match] contains five fields: {Name}, {Link2NewList], {LinkToPastList}, {NewListStr}, and {PastListStr}.
   • {NewListStr}, and {PastListStr} are rollup fields that follow the links to the respective list tables and roll up the primary fields with an ARRAYJOIN() aggregation function.
2. Before performing the import each week
   1. Mark and delete all records from [PastList].
   2. Mark and cut all records from [NewList]; paste them into [PastList].
3. Use the CSV Import Block to import this week’s list into [NewList].
4. Link all records from [NewList] to the single [Match] record. (For instance, copy-and-paste the name of the [Match] record into all cells of the {NewList::Link2Match} column.)
5. In [NewList], define a rollup field that follows the link to [Match] and rolls up [PastListString]with the following aggregation formula:

IF(
    FIND(
        {NewListPrimaryField},
        values
        ),
    'Carryover from last week',
    'New this week'
    )

4. In [PastList], define a rollup field that follows the link to [Match] and rolls up [NewListString]with the following aggregation formula:

IF(
    FIND(
        {PastListPrimaryField},
        values
        ),
    'Carryover from last week',
    'Sold last week'
    )

I think that should work…

[Scott_Heliker]

Holy Smokes lol… Thank you for the detailed work around.
These cars can be changing daily and was tyring to automate everything.

I ping the server for the CSV once a day and send to Air Table and a Facebook Vehicle catalog.

I really just need to say something like if record exist, ignore it (meaing that car is still on the market so just keep that record there) if no match is there, create new record (meaning its a new car just recently added) and then if no record matches from the new upload but the record exist on air table then move it to the sold view or sold table if that’s easier (meaning this car has sold since it’s not part of the new import but exist on air table.

I have to automate this so that I can use the solds view to automate removing images on cloudinary I am creating on the fly to declutter that and to delete post on Google My Business of sold cars and other things.

I thought they would at least have a action to update record, if one does not exist, create new one.

Thanks for the idea but I really need to automate the whole process somehow.

I do have a sold field with a check mark option on the live inventory page. If that gets checked it moves it to the view. Only other option I would have is to have the Car Lot Owner use the app to tick off the solds on Air Table aside from his dealer software.

Possibly I can set some sort of filter in Integromat to use the search actions for air table and say if record is not found, mark as sold check box on Air Table… Basically just need to compare the two data points and move any that don’t match to the sold view… Sounds easy enough lol

[W_Vann_Hall]

Well, the CSV Import Block does provide update-if-exist-otherwise-create functionality — and my reply to you initially began, 'The CSV Import Block is your new friend." But then I re-read your post and foundered on your comment that ‘any cars that don’t match have been sold’ and would need to be archived, which resulted in the new/past dichotomy.

Of course, it just dawned on me that your description of the process wasn’t quite accurate – cars that don’t match could either have been sold or be newly offered. Unfortunately, there’s no way of knowing which ones are found in both imports until after processing the import, at which point you have unmatched new and unmatched sold records.

But there are ways to automate[ish] the process, depending on what fields come in from Integromat. (Actually, I suppose you get to decide what Integromat provides.)

OK, how about this: Make sure there is either a CSV import ID number or date stamp. On the import. (That is, each file of imported data will have a unique ID or datestamp; every record in a given import file will have the same ID or date value.) Import using the CSV Import Block, with the import criteria set to update the ID/date. All new and all matched Airtable records will have the most recent import ID/date; any record with the previous ID/date has been sold.

If you use a datestamp and the date is always today’s date, you can tag sold cars automatically; alternatively, you might want to place them in a ‘sold, pending image removal’ state until you can confirm that step.

A lot simpler than my first suggested solution — but, should you ever need to perform a riffle shuffle of automobile ‘for sale’ records, it’s a winner! :winking_face:

[Scott_Heliker]

I see… So does the block import have to be done manually? I do have stock ID and Vin numbers of the vehicles and could time stamp the export from integromat just don’t think you can automate that to the CSV import block with Integro and Air Table current apps correct/ Unless can be done directly through the API?

[W_Vann_Hall]

Yeah, currently I don’t know of any way to automate Block actions, unfortunately.

If you want this to be totally lights-out, you’re probably better off bypassing CSV import altogether and instead have Integromat process individual records. Alternatively, you might want to consider using Zapier to control Airtable – you could keep the rest of your processing in Integromat and pass the information/trigger Zapier through a webhook. Moving to Zapier would allow you to take advantage of Openside’s custom Zapier connector, which offers bulk-processing functionality not available with standard Zapier/Integromat connectors, some of which could be helpful.

[Scott_Heliker]

Nice, never hear of Openside. Thanks.