topic Re: Pick(Match(ValueList in App Development

Pick(Match(ValueList

jworthulf — Tue, 24 May 2022 15:09:52 GMT

Customer Support,

I’m using Pick(Match(ValueList())) to populate my chart, it is working correctly on the first 2 expressions, but will not generate the numbers in the 3^rd expression.

This is what the expression looks like

Pick(Match(ValueList($(exp_Dim)),$(exp_Dim)),
$(e_AdminRate),
$(e_BuildingRate),
$(e_DeliveryMilesRate),
$(e_DeliveryRouteRate),
$(e_Insurance),
$(e_Interest),
$(e_Marketing),
$(e_Multi-UnitRate),
$(e_Sales),
$(e_WarehouseRate))

I have tested the variable and the do calculate numbers correctly

Ex:

Please let me know what I need to do to resolve this issue.

Re: Pick(Match(ValueList

vinieme12 — Wed, 25 May 2022 06:28:06 GMT

can you post a screenshot of the expression editor

and also variable definition for exp_Dim

Re: Pick(Match(ValueList

marcus_sommer — Wed, 25 May 2022 07:03:20 GMT

You may just query match() to see if all value-list entries will be resolved, for example:

Match(ValueList($(exp_Dim)),$(exp_Dim))

Beside of this you may consider to replace the valuelist() with native field-values created within an island-table, like:

dim: load dual(dim, recno()) as dim inline [
dim
a
b
];

and then your expression may look like:

pick(num(dim), expr1, expr2)

- Marcus

Re: Pick(Match(ValueList

jworthulf — Wed, 25 May 2022 13:19:46 GMT

Marcus,

So I did a querry match Match(ValueList($(exp_Dim)),$(exp_Dim))
//,
//$(e_AdminRate),
//$(e_BuildingRate),
//$(e_DeliveryMilesRate),
//$(e_DeliveryRouteRate),
//$(e_Insurance),
//$(e_Interest),
//$(e_Marketing),
//$(e_Multi-UnitRate),
//$(e_Sales),
//$(e_WarehouseRate))
//

And this is what my chart is showing which is what I would expect so when I add the pick function I should the variables should populate the rate field. I checked my variables in a text object and they are calculating the correct numbers.

Is this possibly a version issue? I created this sheet object using version 12.20 and then we updated to 12.60.

Re: Pick(Match(ValueList

jworthulf — Wed, 25 May 2022 13:22:45 GMT

Calculated Dim field

exp_Dim

'Admin',
'Building',
'Delivery_Miles',
'Delivery_Route',
'Insurance',
'Interest',
'Marketing',
'Multi-Unit',
'Sales',
'Warehouse'

Re: Pick(Match(ValueList

marcus_sommer — Wed, 25 May 2022 13:33:11 GMT

I don't think that there is an issue with the releases. I could rather imagine that you have comments within your variables, like:

var:
sum(value) /* expression calculates ...

Applied as a single expression within a text-box it will work but integrated in a bigger expression it will cause problems because the entire variable-string including any comments will be taken and evaluated where it's called.

Should there be any comments - just remove them from the expression and put it into the variable comment-field.

- Marcus

Re: Pick(Match(ValueList

jworthulf — Wed, 25 May 2022 14:16:54 GMT

So I went thru and tested each variable in the expression list and ran the pick(match( querry until I found the variable that was creating the issue.

It turns out the variable was $(e_Multi-UnitRate) when I replaced the variable w/ the expression it was supposed to execute it worked just fine.

If you know why the variable isn't working please let me know.

Re: Pick(Match(ValueList

marcus_sommer — Wed, 25 May 2022 14:29:06 GMT

The expression for this variable looked ok. but the variable-name contains a special char. I never use them within variables - maybe this caused the issue. Therefore just try a replace from:

e_Multi-UnitRate

e_MultiUnitRate

- Marcus

Re: Pick(Match(ValueList

jworthulf — Thu, 26 May 2022 21:08:18 GMT

Marcus,

Thanks for the help, getting rid of the special character took care of the issue!