Looking Up Multi Select Text Field from A Relationship

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered
I have a one to many relationship and I had a look up text value in the child field.  However, recently when I had to convert the parent field to multi select field, I received an error in the child table that this type of fields cannot be looked up.  Is that true and if so what is the work around?
  
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
You will need to add a new lookup field for the Parent into the child, and then look for all usage of the previous lookup field and replace it on reports and forms and possibly formulas with the new field. Then check that the old lookup field is longer being used anywhere and delete it.

It would be helpful to rename your current lookup field to old lookup field, so the new one can have the good name.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Thanks Mark. 
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Mark

Going back to this.  I might have resolved it back then but don't seem to understand.  Are  you saying that I delete the look up and add back to get the multi select field working?  I have a similar issue now.  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
Right, if the parent was say any field type - say numeric and it was looked up to the child as a numeric lookup.  Then you change the field type of the parent to say a text field, then the child table lookup field will get broken. 

You need to look it up again, and then use the usage table on the broken field to locate all uses of the broken field in the child table,  and replace it with the new lookup field and then finally tidy up by deleting the broken field when it is not used anywhere.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Thanks.  It was resolved.  Now there is a little twist there.  If I have a report  in which using a filter that show me all projects where 'Store Type' is blank, it worked.  But after changing this field to multi select and adding back to the report (as it was deleted), I cannot seem to find a way to filter the report based on multi select field.   
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
I'm guessing that the multi select lookup field comes down looking like

apples; peaches; pears

ie a semi colon delimited text field.

The user can use the filter box at the top to type in a type of fruit or else you would need to make a series of formula checkbox fields to calculate of the fruit contains "apple".  So a field called
Apple Yes / no
Peaches Yes / no
Pears Yes/no

But you only get 5 Dynamic filters per reports so it will work if there are less that 5 type of fruits (Stores)    

The low tech solution is to put in a report description to explain to users what to type 

Also you can explain that typing apples OR Pears with the OR in upper case will get hits on both apples and pears.  The OR has to be in upper case.  Also AND in upper case will work too but as an AND.

Mark
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Thanks.  I am actually trying to run a report with multi select field as the filter to show all records where it is not blank. This keeps on showing all records.   
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
Have you put that field on the report to see what the data looks like?  I'm sure that the field is not blank, and hence is on the report.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
I saved the data as spreadsheet and checked values for blanks.  These are blank. Looks like you cannot filter a report based on non blank multi select field. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
I would have to look at your app or do some testing to verify that as it seems unlikely.
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Support desk have advised that this could be a bug.  They need to review this.