Question about automation

Transport_Hub · ‎Mar 12, 2019

Dear Airtable,

I am trying to automate something.

I am having people submit a form. In this form, Column/Question 2 corresponds to a Multiple Select field where they can select their name from a dropdown. Column/Question 3 is where they input a time and then they hit submit

I want the value from Sheet 2 Person A Column 3 to add with the value on Sheet 1 Person A Column 3, value from Sheet 2 Person B Column 3 to add with the value on Sheet 2 Person B Column 3 etc.

I have spent the past 3 days trying to figure it out with rollup and I’m still super confused.

Any help is appreciated.

Thanks in advance!

Transport_Hub · ‎Mar 16, 2019

Nevermind everyone! I figured it out :slightly_smiling_face:

Thanks all!

See Solution in Thread

David_Skinner · ‎Mar 12, 2019

My first questions would be: What do the sheets represent? And what do the columns represent?

It sounds like you are treating Airtable like a spreadsheet rather than a database. If both Sheets 1 and 2 contain the same list of people then you can probably combine the data into one table.

David_Skinner · ‎Mar 12, 2019

I answered before you edited your post.

Question 2 where they select their name should probably be a Link to Record field that links to the other table (rather than a Multiple Select).

Transport_Hub · ‎Mar 12, 2019

Thanks for the reply!

Sheet 1 will have 2 columns;

Column 1 - Name
Column 2 - Hours

Everyone in Sheet 1 will have a predefined number of hours.

Sheet 2 will be linked to a form; once the user submits the form, the info will be logged into the Sheet 2 of the AirTable. Sheet 2 will have multiple columns, but to minimize confusion, we’ll just say Column 3 is hours and Column 4 will be some sort of variable.

What I’m looking for is if I write “ACCEPTED” in Column 4 for one of the rows, that rows’ Column 3 hours get added to Sheet 1 Column 2.

Mac · ‎Mar 13, 2019

I used an IF statement on Table 2 to populate the Table 1 Hours column. IF(Variable,Hours)

You can then link Table 2 records with Table 1 records using your form and use a lookup on Table 1 to bring over the hours that are Accepted.

It might be cleaner to do all of this on one Table, in the manner I described, either the form user or the the back end Airtable user will need to link the record from Table 1 to Table 2. The form would look like this:

Transport_Hub · ‎Mar 13, 2019

Thanks so much for this. I edited your table and made it to my liking.

However, currently, if someone submits the form, the hours are added automatically into Sheet 1 and the end result seems to be “NaN”. How can I do it so that if the variable in Sheet 2 is “Accepted”, only then are the hours added in Sheet 1?

In addition, I made a nested formula in the column “Field 6” and the formula doesn’t seem to pick up duration in time. How do I fix this?

IF({Field 5} < 10, “Kris Cadet Trainee”,IF({Field 5} < 20, “Kris Cadet Pilot”,IF({Field 5} < 40, “Kris Senior Cadet”,IF({Field 5} < 60, “Kris Co-Pilot”,IF({Field 5} < 100, “Kris Senior Co-Pilot”,IF({Field 5} < 140, “Kris Captain”,IF({Field 5} < 200, “Kris Senior Captain”,IF({Field 5} >= 200, “Kris Commander”))))))))

The 10, 20, 40, 60 etc should all be in hours.
Does that make sense.

Thanks so much once again @Mac.

Mac · ‎Mar 13, 2019

Looks like you pulled a formula out of my Table 2, “Table 1 Hours”, which is “IF(Variable,Hours)”. That formula controls whether or not “Accepted” hour make it over to Table 1.

Take a look at this, I think I sorted your NaN error out:

Transport_Hub · ‎Mar 14, 2019

I think I need to edit this so it works with the hh:mm format but I’m not sure how.

In additon, I’d like the original hours to add with the Accepted Hours. Not sure why it isn’t adding anymore.

Transport_Hub · ‎Mar 16, 2019

Any idea anyone? Especially @Mac, you’ve been really helpful :slightly_smiling_face:

Transport_Hub · ‎Mar 16, 2019

Nevermind everyone! I figured it out :slightly_smiling_face:

Thanks all!