Help with formula to select value from column names

[Neal_MF_Harper]

I have a contact list that has rates for contacts, each contact has multiple rates depending on what they are hired for (Photographer, Stylist, assistant…) They may assist one day and style the next day, at different pay rates.

In a separate table for booking the contacts I have created a enum list from the column rates names in the contacts table.

What expression method is best to use so that when the column name is selected from the enum list in the booking form it returns the rate from that column in the contacts table?

Many thanks!

[tony1]

I’m assuming your contacts table looks something like this:

Name, Photographer Rate, Stylist Rate, Assistant Rate
Person1, $200, $0, $50
Person2, $150, $50, $0

It might make more sense to refactor this into a separate rates table that looks like this:

Contact, Position, Rate
Person1, Photographer, $200
Person1, Assistant, $50
Person2, Photographer, $150
Person2, Stylist, $50

Then, you can just look up the rate for a particular contact and position using a SELECT expression.

This has the added benefit that you can easily extend the possible positions without changing a bunch of expressions in your app.

[Neal_MF_Harper]

Great suggestion Tony, thank you!

[Neal_MF_Harper]

Diving into setting up the select expression now and having some challenges, formula returns list when it should be a price:

SELECT(Rate Sheet[Rate], AND([_ComputedName]=[_THISROW].[_ComputedName], [Position]=[_THISROW].[Position]) true)

What am I missing here? Thanks in advance!

[Lynn]

Hi @Neal_MF_Harper Maybe try ANY(SELECT…

[LeventK · Accepted solution]

Neal_MF_Harper:

Diving into setting up the select expression now and having some challenges, formula returns list when it should be a price:

SELECT(Rate Sheet[Rate], AND([_ComputedName]=[_THISROW].[_ComputedName], [Position]=[_THISROW].[Position]) true)

What am I missing here? Thanks in advance!

@Neal_MF_Harper
SELECT expression always returns a LIST. As @Lynn had already stated, wrap your expression with ANY(…)

[Neal_MF_Harper]

Just wanted to followup with a thank you! Worked great.