0

Formula for On Click field to change the value manually by typing that is display from a reference table.

Hi, I have a form which show me fields with values from different tables like for expample the carton measurements Length, Width and Height. However, sometimes it happen that the measurements differs and needed to be updated for the specific shipment. How can I create a formula, that when I click on the field that I can change the size of the carton manually by typing in but without changing the values from the referenced table. Thanks.

11 replies

null
    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    My first thought is something like this, with numeric fields and Yes / No checkbox fields. The checkbox fields could have code similar to this:

     

    if 'Edit Height' = true then
      Height := null
    else
        if 'Edit Height' = false then
          Height := first(Cartons.Height)
        end
    end

    Shipments Cartons

    • sebastian
    • 3 yrs ago
    • Reported - view

    Hi Dean, thanks for the advice. Does this only work with numeric fields or also with formula fields? I am using formula fields for the sizes Lenght, Width and Height to connect to the source table as I couldn't figured it out how to connect numeric fields to the source.

    Screenshot 2020-09-20 at 11.42.26 AM

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    Only with numeric fields.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    Another approach using a Choice field with "Trigger after upate" set to:

     

    if text(Choice) = "Edit" then
       Height := null;
       Width := null;
       Length := null
    else
       let c := text(Choice);
       let t := (select Cartons where Description = c);
       Height := first(t.Height);
       Width := first(t.Width);
       Length := first(t.Length)
    end

    Shipments Cartons2

    • sebastian
    • 3 yrs ago
    • Reported - view

    Hi Dean, thanks alot. That worked like a charm. Needed to make some changes to the fields, making them all to numeric fields and add the codes to trigger after update (table formula and the field formula).

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    Just to add some diversity...

    Since the "Carton" table is linked, I suppose that you have, in the "Shipments" table, a n:1 reference field called, by default, "Carton". In its "Trigger after update" optional configuration field, you could put:

    Height := Carton.Height;
    Width := Carton.Width;
    Length := Carton.Length

    This way, after selecting a linked carton record, the numeric fields in the shipment depicting the dimensions are initialised, and you can then modify them if needed. If you feel that it could sometimes be necessary to reinitialise them to the defaults for the chosen carton, define a button with the same script.

    • Choices_Software_Dean
    • 3 yrs ago
    • Reported - view

    I like Alain's suggestion best. It replaces the Choice field with a n:1 reference field, which acts similar to a pop-up with an unlimited number of items. Plus it requires less code. Thank you Alain.

    • sebastian
    • 3 yrs ago
    • Reported - view

    Hi Alan, Thanks for advice. If I follow you, does it mean with this formula, if I click on the data field where the Lenght, Width and Height are recorded they would be configureable and changeable? That would be the solution to my initial question. Thanks a lot. I will try this out.

    • sebastian
    • 3 yrs ago
    • Reported - view

    Hi Alan, Thanks for advice. If I follow you, does it mean with this formula, if I click on the data field where the Lenght, Width and Height are recorded they would be configureable and changeable? That would be the solution to my initial question. Thanks a lot. I will try this out.

    • sebastian
    • 3 yrs ago
    • Reported - view

    Hi Alan, Thanks for advice. If I follow you, does it mean with this formula, if I click on the data field where the Lenght, Width and Height are recorded they would be configureable and changeable? That would be the solution to my initial question. Thanks a lot. I will try this out.

    • Alain_Fontaine
    • 3 yrs ago
    • Reported - view

    Since the Length, Width and Heigth fields must be Number fields for this solution to work, they are indeed changeable. The role of the script is only to write the default values when a "Carton" record is chosen, but after that the fields are yours entirely. This is perhaps what you were trying to achieve before resorting to Function fields, which copy the reference values at all times, and are thus not editable.

Content aside

  • 3 yrs agoLast active
  • 11Replies
  • 1658Views