Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here
Jul 10, 2021 07:53 PM
Hello,
I am looking for a method/formula to calculate the total time allocated for products (for manufacturing) which have different components with pre-defined times.
For instance, A product called “GS60” can be manufactured from one or mix of “RCBO+GPO”, “SA Meter”, “Modem” and so on. I have pre-defined time for each component such as “RCBO+GPO = 1h”, "SA Meter = 1:5h etc.
Once I select the components from multiselect field, it should be calculated the total time for each product in “Total Time” field.
Thanks
Solved! Go to Solution.
Jul 10, 2021 10:07 PM
Welcome to the community, @Asanka_Napagoda! :grinning_face_with_big_eyes:
For each piece, you could use the FIND()
function to determine if it exists in the {Components}
field, then check to see if that number is greater than zero. For example:
FIND("RCBO+GPO", Components) > 0
That expression will return 1 or 0: 1 if the item is found, zero otherwise. Multiply that result by the number of hours that this component requires.
(FIND("RCBO+GPO", Components) > 0) * 1
That will return the appropriate time if the piece exists, or zero if not. Add all of these calculations together, and you get the total time for all selected pieces. Here’s a slightly larger example:
((FIND("RCBO+GPO", Components) > 0) * 1) +
((FIND("SA Meter", Components) > 0) * 1.5) +
((FIND("Modem", Components) > 0) * 0.75) +
...etc
I’m working on a course all about Airtable formulas, and it includes little logic tricks like this. I’m making good headway with it, and hope to have it out within the next month or two. Keep an eye on the forum for more details!
Jul 10, 2021 10:07 PM
Welcome to the community, @Asanka_Napagoda! :grinning_face_with_big_eyes:
For each piece, you could use the FIND()
function to determine if it exists in the {Components}
field, then check to see if that number is greater than zero. For example:
FIND("RCBO+GPO", Components) > 0
That expression will return 1 or 0: 1 if the item is found, zero otherwise. Multiply that result by the number of hours that this component requires.
(FIND("RCBO+GPO", Components) > 0) * 1
That will return the appropriate time if the piece exists, or zero if not. Add all of these calculations together, and you get the total time for all selected pieces. Here’s a slightly larger example:
((FIND("RCBO+GPO", Components) > 0) * 1) +
((FIND("SA Meter", Components) > 0) * 1.5) +
((FIND("Modem", Components) > 0) * 0.75) +
...etc
I’m working on a course all about Airtable formulas, and it includes little logic tricks like this. I’m making good headway with it, and hope to have it out within the next month or two. Keep an eye on the forum for more details!
Jul 11, 2021 12:11 AM
@Justin_Barrett :grinning_face_with_big_eyes: Genius. That does the job. Thank you so much.