Dynamic field names without if

[Anonymous]

Hello!

I need some input for the following scenario.

We have articles, which are sold in varying batch sizes. e.g. single bottles, batch of 6 bottles etc.

Field: BatchSize

Summing up the sold numbers of different batch sizes doesn't make sense. What does 1 batch of 6 bottles plus 2 bottles equate to? 3? 8? You know what I mean...

Still, when only one batch size is selected we want to show the number of sold batches, not of single bottles. So we created two fields, one for the sold number in single bottles (QuantityInSingleItems) and one for the batches (Quantity).

Condition:

LET $OnlyOneBatchSize = count(distinct BatchSize) = 1;

Dynamic field formula:

LET $QuantityField = $(=if(

    $(OnlyOneBatchSize),

    'Quantity',

    'QuantityInSingleItems'

))

So we use in the chart:

sum($(QuantityField))

But this (as far as I understand it right now) will result in if-evaluations in every row. I thought it would be evaluated once, due to the several levels of $ sign expansion/evaluation, but doesn't seem to be the case. We could split the formula into two and use $OnlyOneBatchSize as a condition for the visibility, but there are two more conditions of this kind and that would result in a whole lot of formulas. Macros for setting the condition variables are something, which I'd like to avoid for reasons of maintenability.

Please advice and hint on best practices. Right now (with a few those if-this-then-that-field-else-the-other-field) formulas in the application, the responsiveness has suffered quite a bit.

Regards,

Sandro

[daveamz]

See the example from attach.

Hope is what you need.

Best regards,

David

[Anonymous]

Hey David, thank you for the sample application.

That is roughly my approach as well. But there you have the if-formula in the diagram, which will be evaluated in every single row and that is what causes my performance problems with a larger number of rows

[Anonymous]

Hello Massimo!

That equality sign there might really do the trick. I saw that yesterday in the link you provided and tried it out. At first it seemed to greatly improve things. Then when switching back to the old formulas (without the equality sign) I could not reproduce the bad performance though ;-(

I'll try again today with more data and let you guys know. I think I learned something really helpful here 🙂

Sandro

[daveamz]

Hi Sandro,

Yes, it will affect performance a bit, but is a simple IF statement, with only 1 condition which evaluates the Selected fields, not every single row.

Another way it will be to have 2 expressions with conditional (for QV v11):

1. sum(QuantityInSingleItems/BatchSize) with conditional : GetSelectedCount(BatchSize)>0

2. sum(QuantityInSingleItems) with conditional: GetSelectedCount(BatchSize)=0

Regards,

David

[Anonymous]

I looked at it again and when using the = in front of the formula, the performance is much better. Thank you guys! 🙂