+4Table 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
Best answer by Kamille_Parks11
@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.
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.
+27You 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)?
+4You 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)?
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:
+27Thank 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:
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?
+29Thank 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:
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.
+4@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.
+27@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.
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.
+4Yes. 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.
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.
+27This 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.
No. No calculated field (formulas, rollups, lookups, etc.) can appear on a form.
+4No. No calculated field (formulas, rollups, lookups, etc.) can appear on a form.
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.
+35You can”t add calculated fields to Airtable’s forms, but you can do this with Fillout’s advanced forms for Airtable.
To add calculated fields to your Fillout form, just add any text element onto your form and type the @ symbol. Then, you will get a whole bunch of additional options of what you can display on your form, including calculated fields.
You can pull these calculated fields from linked records in Airtable, or you can create your own basic calculations in Fillout, or you can pull from an Airtable record that you are updating in Fillout, or you can pull calculated fields from any of these 4 prefill methods.
Even better, Fillout is 100% free, and it offers hundreds of features that Airtable’s native forms don’t offer, including the ability to update Airtable records using a form, create custom PDF files from a form submission, accept payments on forms, pre-fetch dynamic data from an Airtable record, create new linked records on a form, add a login page to your form, perform math or other live calculations on your forms, collect signatures on a form, create multi-page forms with conditional paths, connect a single form to dozens of external apps simultaneously, add CAPTCHAs to your form, and much more.
I show how to use a few of the advanced features of Fillout on these 2 Airtable podcast episodes:
Hope this helps!
If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.