Lookup value in table

[cjs321]

Hi,

I'm stuck on finding the correct syntax..

Issue: I need powerapps to lookup a value in a table (BRTLookupTable).

Columns in the Table (In order): From;To;A;B;C;D;E;F

The user should select a Belgium Road Tax zone from a dropdown, i.e BE-1, i've then put an IF statement together, that says, if BE-1 is selected, then, the BRT Zone is E etc.

Powerapps then needs to find a number value between the From and To columns.

so, let's say the weight of a delivery is 23000 kg, in my table, the last row, is From 18001 kg to 25000 kg, how do get powerapps to know that it should be looking on the last row, and column E to get the value it needs?

Kind Regards,

Con 🙂

[WarrenBelz]

HI @cjs321 ,

Now I have seen your table, there is a fundamental issue I should have picked up on the last post and my apologies for this.

There is a third criteria to the values and you are correct, I have been following flawed logic - an additional value is required. PowerApps unfortunately does not allow variables to be used to substitute column names.

Timezone here will see me offline shortly - I will have a think about it and get back to you as it may be quite complex

[cjs321]

Hi @WarrenBelz ,

No worries, thanks for the help 🙂

[WarrenBelz]

OK @cjs321 ,

You did not say what was not working - lets leave the matching values in brtFrom/brtTo for the moment - there has got to be a solution to that. If you put in a label and try Value(brtTo) and if you get an error, it is already a number.

Another thing to try - disconnect and reconnect your Excel data source - any change of column format may not have registered.

On the the last bit - calling the DropDown (which has been forgotten about for a while) DropDown1 and the items from it DropDown1.Selected.Value (as it would be on a Choices field). Firstly I would set a Variable on DropDown1 OnChange

UpdateContext({vddValue:Dropdown1.Selected.Value})

then in the Lookup

Lookup(
   BRTLookupCollection,
   ldmokg >= Value(BRTFrom) &&
   ldmokg <= Value(BRTTo),
   Switch(
      vddValue,
      "A", A,
      "B", B,
      "C", C,
      "D", D,
      "E", E,
      "F", F
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[cjs321]

Hi @WarrenBelz,

Still doesn't work 😞

LookUp( BRTLookupTable, 23800 >= Value(BRTFrom) && 23800 <= Value(BRTTo), Switch( vddValue, "A", BRTLookupTable.A, "B", BRTLookupTable.B, "C", BRTLookupTable.C, "D", BRTLookupTable.D, "E", BRTLookupTable.E, "F", BRTLookupTable.F ))

Returns an error: Expected Text Value

You'll notice in the above formula, i've opted not to use ldmtokg for now.

[WarrenBelz]

Hi @cjs321 ,

There has got to be something wrong with brtFrom and brtTo values. They need to be numbers or the filter simply will not work. What do you get with

LookUp( 
   BRTLookupTable, 
   20000>= BRTFrom && 24000 <= BRTTo, 
   "A"
)

[cjs321]

Hi @WarrenBelz ,

This time, i don't get a full syntax error! Only the BRTFrom and BRTTo values have errors.

Error: Invalid Argument, Expecting a Number Value.

Isn't that because BRTFrom and BRTTo are headings in the BRTLookupTable / Collection?

[WarrenBelz]

Hi @cjs321 ,

That is because 20000 is a number value and BRTTo and BRTFrom are not.

• put a new Edit form on a new screen and make
• DataSource BRTLookupTable
• Item - First(BRTLookupTable)

and see the values in these fields.

[cjs321]

Hi @WarrenBelz ,

I've just done that, then what do i do? 🙂

[WarrenBelz]

Can you see the values you are expecting in these two fields?
Also taking the control name of BRTTo, put a two labels on the screen  - one with Value(YourControlName) and the other Text(YourControlName, "###,##0.00").  We really need to see what this control is returning.

I am going to do some tests with your Excel sheet here as well

[cjs321]

Hi @WarrenBelz ,

I will await your test results 🙂