Forum Discussion

SonaAdmin's avatar
SonaAdmin
Qrew Trainee
24 hours ago

Formula for form fields

Hi want to write a formula to show and hide fields on a form which is in Table 3.

Table 1 has one to many relation ship with Table 3. (not sure if it even matters)

Table 2 has one to many relation ship with Table 3(not sure if it even matters)

So few questions on this?

  1. Do i write this formula in Table3 - Advance Settings > Build custom data rules for this table: > in this field?
  2. How do i control where these fields will show up on the form and alignment ?
  3. How do i hide all fields initially, just remove them from the form ?
  4. Also Table3_Form_Field1 = will be selected by the user when they create a new record in Table3 so is this going to work dynamically or i need to use the dynamic form rules only instead?
  5. If the formula will do the job then, Is this the right formula structure?
formulas

2 Replies

Sort By
  • SonaAdmin's avatar
    SonaAdmin
    Qrew Trainee
    24 hours ago
    1. If the formula will do the job then, Is this the right formula structure? below is the formula

    If (

    [Table3_Form_Field1] = "Value1",

    Then show [Table1_Field1], [Table1_Field2],

    else if (

    [Table3_Form_Field1] = "Value2" or "Value3",

    Then show [Table2_Field1], [Table2_Field2],

  • Laura_Thacker's avatar
    Laura_Thacker
    Qrew Captain
    22 hours ago

    Actually you could do either; since these are lookup fields.

    FORM RULE with HIDE/SHOW solution: 

    Your rule will look like this:

    When

    [Table 3 Field 1] = "value 1"

    then

    show [table 3 field 1]

    hide [table 2 field 1]

    hide [table 2 field 2]

    This logic means that when the table 3 field 1 SHOULD be visible; then it will be and under all other conditions it will be hidden.  Conversely by adding the hide table 2 field 1 and 2; you're saying that when table 3 field 1 SHOULD be visible, then the other 2 fields should not be visible.  So when table 3 field 1 is not visible the other table 2 field 1 and 2 WILL be visible.

     

    FORMULA:

    If( [table 3 field 1] = "value 1", [table 3 field 1], [table 2 field 1] & " " & [table 2 field 2])

    (this assumes your table 2 field 1 and 2 should be displayed "together" in a single field.  Also, this formula will "persist" across the entire table which would include reports, forms, formulas and emails etc.  So this might be a better solution that one simple form rule that only exists in that one state.  Using this formula in this way you can turn off reporting for the 3 lookup fields and rely only on this one formula.