End of Month Wrap-up Automation

[nathan_bowman]

Hi all,

I'm working on moving my team's operations over to Airtable, and I'm trying to find a way to automatically compare records in different tables at the end of the month.  I'm pretty sure I'll need to use scripting, but I'm wondering if there's a no-code or low-code way to do this instead of fumbling my way through javascript with chatGPT. 

The use case is:

I have a base (LIST1) that is a list of sites we operate at every month, and another base (LIST2) that is a junction table documenting our activities (joining other bases of staff, vehicles, sites, equipment, etc).  I'm trying to set up an automation that fires at the end of every month to see if there are any sites we didn't visit that month and if so add those to a third base (LIST3) as a new record for each 'missed' site. 

The plan is to

• Find all records from LIST1 that have the inspection interval "monthly" - I CAN DO THIS 
• I CAN DO THIS - Find all records from LIST2 that were inspected in the last month - I CAN DO THIS
• Compare and look for duplicates.  If there are duplicates, add them to ARRAY1.  If there are not duplicates, add them to ARRAY2.  - I NEED HELP WITH THIS
• Write everything from ARRAY 2 to LIST3 - the list of 'missed' sites, along with some information about the date/referenced records.  - I CAN DO THIS

Can anyone help me figure out a way to "compare" two lists of found records?

[o_elsiefy]

For the duplicates thing, there may be a work around using roll-up and the count function (if count is >1 then create a new record in Array 1, if count = 1 create a new record in Array 2) this will depend on the field type you use to identify if something is a duplicate or not.

[nathan_bowman]

That's clever!  I'm not sure if it would work though - the field type is a linked reference - so every entry in LIST2 is linked to at least one record in LIST1.  One inspection can cover multiple sites, in other words.  

I really don't have any way to ID if it's a duplicate or not.  If I could nest a conditional logic block inside of a group action (i.e. an if statement nested in a for loop) I could do this trivially with no-coding, but right now I can't figure out a way to iterate through the lists and find the correct one.

[o_elsiefy]

@nathan_bowman what do you consider a duplicate? Ex. when two things have the same "Name" or when two entries pertain to the same site

If you are able to group your sheet by a field that would have 2 or more items in each group if they are duplicates, or 1 item if there are no duplicates, then you can do what I'm telling you above.

If your definition of a duplicate is more complex than that, you'll probably need a script, but word of advice, hire someone that can write Java because I tried the whole ChatGPT thing to write a script on airtable and it didn't work too well. The best thing chatGPT works for is formulas, the Airtable scripting tool uses very low-level Java, that even my developers struggle to work with sometimes.

[jsep]

Right now there is no way to compare those values with no-code solution in automations, you will have to use an script to do that.

I can help you with that is very simple script, feel free to schedule a 15min call with me.

[o_elsiefy]

I do it by setting conditions in my automations such as "count is >1" and it works fine. But for the case of @nathan_bowman it would depend on how his data is structured.

[nathan_bowman]

@nathan_bowman what do you consider a duplicate? Ex. when two things have the same "Name" or when two entries pertain to the same site

It's a little of both - there is only one factor that's going to change (LIST2, the list of inspections) and that's going to be linked to a few specific entries on LIST1.  I just want to make sure that everything on LIST1 is linked to something on LIST2 in a given timeframe.  If something isn't linked in that timeframe, I want to make a note of it somewhere so I can send out a "hey, we forgot these sites!" email.

[o_elsiefy]

I have an idea for that actually, if that's your objective, I presume you'd be sending this email close to the end of the month. You can setup your automation to send the email whenever the link field is empty and have it trigger at certain time or link it to a button and you can manually run it to send a reminder

[ScottWorld]

Hi @nathan_bowman,

If you're looking for a way to do this in a no-code way without any scripting at all, you could do this with Make's advanced automations.

However, this would involve some very advanced knowledge of Make, and there is quite the learning curve with Make. You'd also need to know how to work with arrays and variables in Make.

I assembled some Make training resources in this thread, and I show how to work with Make arrays in this video.

In general, the concept would be to create 2 router paths in Make:

Router path 1:
1. Find the records from list 1 and aggregate them into an array (i.e. "list 1 array").
2. Save that array as a variable, so you can search it later.

Router path 2:
1.  Find the record from list 2.
2. Loop through each record in list 2 and see if it exists in the list 1 array.
3. If it doesn't exist, write it to another array, which will eventually become its own list of records in Airtable.

This is easier said than done!

Let me know if you need more help setting this up in Make. And if you'd like to hire an expert Airtable/Make consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld

[TheTimeSavingCo]

Hm, could you provide some screenshots of how your tables are set up, as well as a data example of the output in List 3 based on the data in List 1 and 2? 

Given that you only want to know whether there was something linked within the timeframe, depending on how your data's set up you might not need to do the comparison maybe