0

Dynamic Multiple Choice Field Coding Problem

Hi, I hope I can find here a solutions as I am trying this for some time now without any success.

I am trying to create a Dashboard for partial shipments of orders placed by customers where I can pick of all different products ordered a number out for a partial shipment. I decided to use a Dynamic Multiple Choice Field for this task which is linked (Dynamic Value) to the table 'Ordered Products'.

Everything fine so far and the products are listed in the choice fields for mulitple selection. Now I want to create a trigger when update to record a specific value of a field to a field on my Dashboard (Number Field or Formula Field), lets say 'Weight' to calculate the Total Weight later for the shipment. However, the Weight is not diplayed correctly or the number makes no sense at all and I am stuck burning out my brain.

I am using the formula:

record(Products,number(Choice)).Weight

It won't work.

I created a simple table similar to my project. Hope I can get here some solutions. Thanks alot.

 

Screenshot 2021-06-24 at 1.57.21 PM

Screenshot 2021-06-24 at 1.58.08 PM

Screenshot 2021-06-24 at 1.58.18 PM

7 replies

null
    • sebastian
    • 3 yrs ago
    • Reported - view

    Screenshot 2021-06-24 at 2.04.43 PM

    When clicked on the selections, the values either makes no sense or won't display.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    Dynamic choice fields are weird animals. Your formula would work for a (single) dynamic choice field. Multiple choice are even stranger, and need more elaborated formulas:

    let zSelected := ---
    , { concat(numbers(Choice)) },
    ---;
    sum((select Products where contains(zSelected, ", " + Id + ", ")).Weight)

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    In another style, if you prefer:

    let t := 0;
    for i in numbers(Choice) do
    t := t + record(Products,i).Weight
    end;
    t

     

    • sebastian
    • 3 yrs ago
    • Reported - view

    Hi Alain, Thanks. I wil try the code and see if this will solve my problem I have tried many like your first version but somehow left out the "concat(number....". Will post my effort and result.

    In know that the single dynamic choice field worked with my formular and the work around of my problem would have been to create a subtable for the choices (add each choice a new record with values) and then link sum them up in with a formula field the main parent table. The mulitple dynamic choice field would not need this, if I could come up with a tricky formula 😂. As they say, the less tables you create in your database, the less calculation/work your computer needs.

    • sebastian
    • 3 yrs ago
    • Reported - view

    Both worked. thanks alot. Just only don't understand the "---" in the first version. Yes, they are really weird animals.

    • Sean
    • 3 yrs ago
    • Reported - view

    "---" allows you to assign quoted data to a variable without needing to double all of the quotes. 😎

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    Indeed, the pair of "---" is the alternative string building operator. One can also write:

    let zSelected := ", " + concat(numbers(Choice)) + ", ";
    sum((select Products where contains(zSelected, ", " + Id + ", ")).Weight)

Content aside

  • 3 yrs agoLast active
  • 7Replies
  • 1107Views