Help

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

Assign values (numbers) for each component of Multiselect Field & calculate sum of each combination

Topic Labels: Formulas
Solved
Jump to Solution
457 2
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Capture

Thanks

1 Solution

Accepted Solutions

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!

See Solution in Thread

2 Replies 2

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!

@Justin_Barrett :grinning_face_with_big_eyes: Genius. That does the job. Thank you so much.