Save the date! Join us on October 16 for our Product Ops launch event. Register here.
May 26, 2022 03:10 PM
Bit of a tricky one for me, im looking for a formula for this situation with a multiple select box:
if ‘Jon’ selected =£18,
if ‘Carl’ selected =£20,
if ‘Jon’&‘Carl’ or ‘Carl&Jon’ selected £38
just a snippet of what im after, i have 7 opperatives on different rates, basically looking to then multiply by hours on site to get a final expenditure.
Any help i would be greateful
May 26, 2022 08:13 PM
EDIT: Hey Aaron, here’s how you would accomplish this with a formula, but I do not think you should do this and recommend @Justin_Barrett’s suggestion below instead
We bundle everything into a single formula field that checks a multiple select field
e.g.
IF(
AND(
FIND('Jon', People),
FIND('Carl', People)
),
38,
IF(
FIND('Jon', People),
18,
IF(
FIND('Carl', People),
20
)
)
)
I do not recommend this as it would get super confusing after awhile; and adding / removing people would be a huge pain with modifying the formula
===
Edit: Also deleted my second suggestion that involved multiple formula fields as @Justin_Barrett’s solution is much much better
May 26, 2022 08:54 PM
Welcome to the community, @Aaron_Preston! :grinning_face_with_big_eyes: Because you’ve got data that’s uniquely tied to each worker, I recommend creating a [Workers]
table. Each worker would have a record in the table, and a {Rate}
field would contain each worker’s unique rate.
With that set up, you link the records in your main table to as many workers as needed from the [Workers]
table. To bring in their respective rates, add a rollup field. This would retrieve the value from the {Rate}
field, with a SUM(values)
aggregation formula adding it all together. No matter the order of the links, each worker’s unique rate would be added to the total, removing the need for complex formulas.
May 26, 2022 11:13 PM
Thank you for the response! you can tell im new at this, ive realised over night that the plan i had for these feilds would not work in the grand scheme of thuings for what im trying to achieve due to the mathmatics.
Back to the drawing board i go!
Thank You