Expression to select a value from a related table ...

Scott_Hall · 09-26-2019 05:29 PM

I need an expression formula for this:

If the value of the [Waste Category 1] column in the Service Location Repair table is “Bio” then use the value from the [Bio] column in the Service Location table.

This would let me pull the “bio” price from the Service Location table into the Service Location Repair table based on the waste type.

Can anyone help with this? I’ve learned a lot about these expressions but it feels like my appetite for building this app is always a few steps ahead of my ability to build expressions that work.

Thanks in advance.

Steve

Assuming your Service Location Repairs table has a Service Location column of type Ref to the Service Locations table, use a simple dereference:

[Service Location].[Bio]

Scott_Hall

@Steve I need the value to populate the column based on the selection of an earlier column. I’ve used that dereference expression in other areas but I don’t think it fits here unless I’m missing something.

The Service Location table has a price for each type of waste. In the case “bio”. So if in the Service Location Repair table I’ve selected “bio” as the waste type I need the column I’m working with in Service Location Repair [Price] to populate the “bio” price from the Service Location table.

Am I missing something?

Scott_Hall

@Steve This seems to have worked.

LOOKUP(Biohazardous Sharps, Service Location, Waste Type 1, Bio)

Scott_Hall

Nope. That didn’t work. It is picking up a random value as opposed to the value from the referenced Service Location record.

Steve

How about this?

IFS(("Bio" = [Waste Category 1]), [Service Location].[Bio])

Scott_Hall

@Steve That did it. I then used the following to account for the various possible selections in the enum.

IFS((“Bio” = [Waste Type 1]), [Service Location].[Bio], “path” = [Waste Type 1], [Service Location].[Path], “Trace Chemo” = [Waste Type 1], [Service Location].[Trace Chemo], “Non Hazardous Pharmaceuticals” = [Waste Type 1], [Service Location].[Non Hazardous Pharmaceuticals])

Doing it this way (with an enum) the waste type selections are static. If the user can add, edit and change waste types the expression would fail. How would it look if you simply wanted to say: If the selection in Service Location Repair [Waste Type 1] matches any column in a table named Waste Types, use that value? Understood that the tables would have to be related.

Steve

Your expression might be clearer using SWITCH() rather than IFS():

SWITCH(
  [Waste Type 1],
  “Bio”,
    [Service Location].[Bio],
  “path”,
    [Service Location].[Path],
  “Trace Chemo”,
    [Service Location].[Trace Chemo],
  “Non Hazardous Pharmaceuticals”,
    [Service Location].[Non Hazardous Pharmaceuticals],
  ""
)

Not possible with values distributed across columns as you have them. If instead your service location prices were stored in a separate table called (e.g.) Waste Prices by Location with the columns, (Service Location, Waste Category, Price), you could get the price for an arbitrary waste category with:

ANY(
  SELECT(
    Waste Prices by Location[Price],
    AND(
      ([_THISROW].[Service Location] = [Service Location]),
      ([_THISROW].[Waste Category] = [Waste Category])
    )
  )
)

Scott_Hall

@Steve

I created a table for Price Plan (by location) and tried the Any Select expression (above) but because each location can have several price plans based on waste type and container type combinations this expression does not get the correct result. It selects (I guess) the first price plan option assigned to the location and ignores the others.

A separate table for waste type, container type and price combinations (by location) feels like the right approach as it has all the flexibility a user would want. However I’m having a hard time getting those price plan options to show us as a selection in the Service table.

I created a REF column in the repair form referencing the Price plan table but it picks up all price plans across all locations. I tried using a dependent drop down method figuring I could filter thru the options but it seems that I would have to start with the price plan key to begin the drop down process which isn’t practical and probably has other flaws.

End result I’m looking for is a location, each of which can have several price plans. When completing the service form I’d like to be able to select a price plan and along with it the waste type, container type and price to be included in the service.

Parts of 2-3 days of racking my brain and destroying my test app has not yielded any encouraging results. Thoughts? Ideas?

Steve

Yes, that’s what ANY() does: gets the first item from a list.

Of course, you didn’t mention this earlier, so my proposal couldn’t take it into account.

It sounds like you could use dependent drop-downs for this:

Expression to select a value from a related table based on the value of a column in current table