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.

11replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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

    Like
    • sebastian
    • sebastian
    • 1 yr 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

    Like
  • Only with numeric fields.

    Like
  • 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

    Like
    • sebastian
    • sebastian
    • 1 yr 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).

    Like
  • 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.

    Like
  • 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.

    Like
    • sebastian
    • sebastian
    • 1 yr 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.

    Like
    • sebastian
    • sebastian
    • 1 yr 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.

    Like
    • sebastian
    • sebastian
    • 1 yr 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.

    Like
  • 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.

    Like
Like Follow
  • 1 yr agoLast active
  • 11Replies
  • 1413Views