Forum Discussion

JamesCarr's avatar
JamesCarr
Qrew Member
4 years ago
Solved

Changing Record Picker List Dynamically

Here's the situation:
  • Table A is the parent to table B
  • The reference lookup field's record picker in table B is based on Report1 from Table A
  • There is also Report2 from Table A that the record picker can be based on
Is it possible to have the record picker for the lookup change from Report1 to Report2 based on a checkbox (or some other kind of field) on the form for table B?  I have tried to copy the look up field, rename it, change its record picker to Report2, and then use dynamic form rules to set which field is visible for the user based on a checkbox, but the result is both lookup fields are visible or hidden at the same time.  I cannot figure out how to hide one and not the other.  I, also, cannot figure out another way to create this setup, so users can see options to choose from based on Report1 or Report2, depending on whether or not a checkbox is checked.

I hope this explanation was clear enough for someone to offer some suggestions / guidance.  If more details are needed, let me know where you are not clear, and I will do what I can to fill in the gaps.  Thanks in advance for any help anyone can give.

------------------------------
James Carr
------------------------------
Conditional-Dropdown
Tables and fields
  • MichaelTamoush's avatar
    MichaelTamoush
    4 years ago
    No, the related field (or its proxy) is what you will still use. However, under field properties (of the related field) there is a check box for conditional dropdown. Select that, and then it will ask you what field on the current table, should match what value on the parent table.

    On the current table, you are matching a formula field you create to another formula field you will create on the parent table. The formula is where all the magic happens.

    So on the parent table (lets say it was an employee table with salespeople, managers, and accounting) and lets assume your original Report 1 filtered only salespeople, and your original Report 2 filtered only Managers. Your formula would be, if([TItle]="SalesPeople","Report1", [Title]="Managers", "Report2".  Now your entries on your parent table are labeled Report 1 or Report 2.

    Jump back to child table. All you need is a field where they select Report 1, or report 2. This can be a multiple choice field, or you can use a check box and then create a formula text field to output Report 1/Report 2. (if [checkbox]=true, "report1","report2" ).

    The conditional dropdown you set is matching both of those fields. You still use your related field.

    I'm not sure I understand your question: "will a dynamic form rule allow the lookup field to have the value in the formula field once the formula field changes". But perhaps moot with the explanation above.

    ------------------------------
    Mike Tamoush
    ------------------------------

7 Replies

Sort By
  • BlakeHarrison's avatar
    BlakeHarrison
    Qrew Captain
    4 years ago

    While you can't dynamically shift the report selection used for the record picker, you can create a dynamic formula whose value changes based on criteria and then use THAT as the first field in the record picker. I used the below example in a table that's used for managing inventory. The user needs to be able to see various pieces of information in the drop-down, but they're not always present:

    List(" | ", If([Part #]<>"","Part #: " & [Part #],null), If(Reference]<>"","Ref: " & [Reference],null), If(
    >0,"Code: " & 
    ,null),
    If([Condition]<>"","CD: " & [Condition],null)) & 
   If([Description]<>"","Desc: " & Left([Description],25),null)

    ------------------------------
    Blake Harrison
    bharrison@datablender.io
    DataBlender - Quick Base Solution Provider
    Atlanta GA
    404.800.1702 / http://datablender.io/
    ------------------------------
  • MichaelTamoush's avatar
    MichaelTamoush
    Qrew Captain
    4 years ago
    Can you do it with a conditional dropdown rule?

    i.e. on Parent Table A create a formula text field called [Report Choice] that labels each record Report 1, Report 2, depending on which report it would normally fit into (if you were using reports). On child table create a formula text field titled [Report Choice] that says, if [Checkbox]=true,"Report 1","Report 2".

    Conditional rule that says: If value in this table, Report Choice matches value in parent table Report Choice

    Essentially, instead of filtering with a report, you are using a formula field on the parent to mark each entry that would fit into your report.

    ------------------------------
    Mike Tamoush
    ------------------------------
    • BlakeHarrison's avatar
      BlakeHarrison
      Qrew Captain
      4 years ago
      I don't see why not.

      ------------------------------
      Blake Harrison
      bharrison@datablender.io
      DataBlender - Quick Base Solution Provider
      Atlanta GA
      404.800.1702 / http://datablender.io/
      ------------------------------
      • JamesCarr's avatar
        JamesCarr
        Qrew Member
        4 years ago
        Thank you both for responding. It sounds like the conditional drop-down idea would be more ideal. However, it sounds like it would be replacing the reference lookup field with a new formula field. Is that correct? If so, will a dynamic form rule allow the lookup field to have the value in the formula field once the formula field changes?

        ------------------------------
        James Carr
        ------------------------------