Can Flow field be used in Setrange outside the repeat loop

samantha73
samantha73 Member Posts: 121
in NAV Three Tier

ChatGpt tolder me I cannot compare flowfields in setrange (after using calcfields) like below, is it true or inefficient

salesheader.setrange(Flowfield1, Flowfield2)

If salesheader findset() then

repeat

Until

FlowFields Cannot Be Used in SetRange Filters

FlowFields are calculated fields - they don't exist as physical columns in the database table. They are computed on-the-fly based on their CalcFormula.

Business Central's SQL engine cannot translate FlowField comparisons into WHERE clauses because:

  1. FlowFields are virtual - there's no column in SQL Server to filter on
  2. Each record's FlowField value requires a separate sub-query to calculate
  3. SetRange on a FlowField would need to calculate EVERY record first, then filter - extremely inefficient

Best Answer

  • vaprog
    vaprog Member Posts: 1,173
    Answer ✓

    FlowFields Cannot Be Used in SetRange Filters

    is wrong (at least in BC, and I believe even in the latest versions of NAV).

    Business Central's SQL engine cannot translate FlowField comparisons into WHERE clauses

    is wrong. Reason 1 is plain stupid wrong; reason 2 is no reason for the claim and is not necessarily true; reason 3 is no reason for the claim and is worded in a misleading way. The SQL engine will have a query optimizer and will likely calculate the value for the flowfield only after having rejected records based on other conditions if it is more costly than to evaluate the other conditions.

    What is the alternative to filtering on the flowfield? You would have to retrieve data for all candidates, calculate the value of the flowfield so you are able to compare against it in code. So, you are doing the same work, but have to transfer more data from the database.

    Unless you have more conditions you need to check in code (i.e. cannot specify as filter expressions) which can be executed more efficiently than calculating the value of the flowfield, letting the SQL engine execute the condition on the flowfield may be more efficient; still costly, but more efficient. In this case you would need to calculate the flowfield with a CalcFields statement, not using the autocalfields feature to possibly gain anything. But you would then need an additional call to the database to get the calcfield value. So, in the end, still unlikely to be more efficient.

Answers

  • vaprog
    vaprog Member Posts: 1,173
    Answer ✓

    FlowFields Cannot Be Used in SetRange Filters

    is wrong (at least in BC, and I believe even in the latest versions of NAV).

    Business Central's SQL engine cannot translate FlowField comparisons into WHERE clauses

    is wrong. Reason 1 is plain stupid wrong; reason 2 is no reason for the claim and is not necessarily true; reason 3 is no reason for the claim and is worded in a misleading way. The SQL engine will have a query optimizer and will likely calculate the value for the flowfield only after having rejected records based on other conditions if it is more costly than to evaluate the other conditions.

    What is the alternative to filtering on the flowfield? You would have to retrieve data for all candidates, calculate the value of the flowfield so you are able to compare against it in code. So, you are doing the same work, but have to transfer more data from the database.

    Unless you have more conditions you need to check in code (i.e. cannot specify as filter expressions) which can be executed more efficiently than calculating the value of the flowfield, letting the SQL engine execute the condition on the flowfield may be more efficient; still costly, but more efficient. In this case you would need to calculate the flowfield with a CalcFields statement, not using the autocalfields feature to possibly gain anything. But you would then need an additional call to the database to get the calcfield value. So, in the end, still unlikely to be more efficient.

  • RockWithNAV
    RockWithNAV Member Posts: 1,195

    Did you try doing some sample code and than see the result?

    Simple piece of AL code like -


    Customer.SetRange("No.", 'C10000');
    Customer.SetRange("Balance on Date", 0, 20231231D);
    if Customer.FindFirst() then
    Message(Format(Customer."Balance at Date"));

    Thanks

    Blog - rockwithnav.wordpress.com/
    Twitter - https://twitter.com/RockwithNav
    Facebook - https://facebook.com/rockwithnav/
  • samantha73
    samantha73 Member Posts: 121

    Thanks for the answers , yes I did use flow fields on setrange and it worked however when the AI suggest code it always include calfield and flow fields inside the repeat loop so i though i'm missing something - to be fair to ChatGpt above AI statement is given by Claude Opus 4.5 supposed to be the latest and greatest :) …but one can argue if the models are trained on human forums and internet posts then there is some previous knowledge gap expressed and model just picked up that. interesting times

  • RockWithNAV
    RockWithNAV Member Posts: 1,195

    Ya I agree that's why I said - Did you try this? 😊

    CALCFIELDS are needed in Repeat Until because the value is continuously changing inside the loop and system needs to recalculate and give the correct data.

    Thanks

    Blog - rockwithnav.wordpress.com/
    Twitter - https://twitter.com/RockwithNav
    Facebook - https://facebook.com/rockwithnav/

Categories