Automatically link to next record in another table

[Holli_Younger]

Table 1 is a database of completed projects, table 2 is a list of invoices. How do I have the linked field in table 1 link to the next available invoice # in table 2? The invoice #s cannot be duplicated and I’d like them to stay chronological. All of this is currently set up, but it’s done manually. First, by searching what invoice # is next then in table 1 under the invoices field, create a new record with the next invoice # in line. Is there a simpler way to do this?

Table 1

Table 2

[Kamille_Parks]

You can set up an Automation to create a new Table 2 record whenever a new Table 1 record is created, and link those two together.

{Invoice #} field in Table 2 is a formula, how is that being calculated? It appears to already be incrementing so is it the case that you just need a new Invoice record (Table 2) to be auto-connected to a new Project record (Table 1)?

[Holli_Younger]

Thank you Kamille, unfortunately, I don’t think automation will work for this. Because the records on Table 1 are jobs, some might have multiple invoice numbers associated, where others might not have any. Another issue with the automation I’ve found is if I need to create (and link) an invoice # at a later time. The automation doesn’t understand this, or maybe it’s me? I’m sure a script could do it, but that’s way out of my league and if changes need to be made, I’d be stuck. :slightly_smiling_face:

[Kamille_Parks]

I am not clear on what issue you’re trying to solve. As I mentioned above, your Table 2 already seems to be incrementing the Invoice #. You mention a manual process but the {Invoice #} field you’re showing is a formula.

When you’re in the Jobs table and linking records to Invoices, there is a “Add new record” button at the bottom. If your records are incrementing on their own, clicking that button would be all you need to do.

So again, how is {Invoice #} being calculated?

[kuovonne]

Holli_Younger:

I’m sure a script could do it, but that’s way out of my league and if changes need to be made, I’d be stuck.

It is possible to automate a system that does not use a script, but that system might be more complex and confusing to maintain than simply using a script.

You are also smart to recognize that it is difficult for a non-coder to maintain a script. Here are some ways of dealing with the situation:

• Decide if the benefits of having a script to streamline your workflows outweighs the possibility that you will need to make changes to the script.

• Hire someone reputable to write the script so that you can trust that the script will work correctly in all edge case, minimizing the need for bug fixes.

• Pick a script writer has a clear writing style and documentation/comments so that when if the original script writer is unavailable, another script writer could take over the script and make any adjustments.

• Have a script that minimizes the amount of information that needs to be hard coded (such as table and field names) and put anything that needs hard coding in a clear section at the top of the script so you can easily adjust field and table names without messing with the rest of the code.

[Holli_Younger]

@Kamille_Parks The biggest issues come from non-tech-savvy employees who get confused when a record isn’t visible to select (like other options throughout the base), or when they click the “Add New Record” option they want to continue filling in other details. Since there is no way to hide the hidden fields from their view in the pop-up window they get 'carried away with data entry, causing an error in the workflow on Table 2. Is there a way to create a button linked to a form to fill in only the necessary fields for Table 2?

@kuovonne Very good advice, thank you. I’ve seen a lot of really great scripts online and through the community, but even with instructions sometimes they are hard to follow when you don’t have any experience in scripting. I’d rather stray away from scripting because of the high possibility of future changes in Airtable, we are always updating.

[Kamille_Parks · Accepted solution]

Holli_Younger:

Is there a way to create a button linked to a form to fill in only the necessary fields for Table 2?

Yes. Create a Form View for Table 2 that includes all the necessary fields including the Link to Table 1. Get the share link for that form view and assign it to a Button field in Table 1. If you want the form to be prefilled with whichever Invoice record on which the button was clicked, you could use Kuovonne’s nifty app to generate what the URL formula would be.

Airtable

Prefilled Forms - Apps - Airtable Marketplace

Prefill forms with field values on Airtable with the Prefilled Forms app.

[Holli_Younger]

This is perfect @Kamille_Parks. Thank you, I will mark your answer as the solution, but didn’t want the thread to close until I asked one more question for you regarding the prefilled Forms app from @kuovonne. Is there a way to add roll-up fields to the form? They are on the grid view, but there is no option for them in airtable forms.

[Kamille_Parks]

Holli_Younger:

Is there a way to add roll-up fields to the form?

No. No calculated field (formulas, rollups, lookups, etc.) can appear on a form.

[johngiordani]

I see this thread ended in 2021...is this still a true statement?

Or has Airtable advanced past this in the past 3 years?

Is there a way to add roll-up fields to the form?

No. No calculated field (formulas, rollups, lookups, etc.) can appear on a form.