Text Field with list of values from assignment table

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Hey Folks!

I have a two tables that have many to many relationships between them using an assignment table as such:

Records < Country-Record Assignments > Countries

I want a text field in the Records table that will list all of the Countries it has been assigned.  So for example, if a Record was assigned to United States, Canada, China, and India, the value of the field in the Record table could be "Canada, China, India, United States".

I need to be able to retrieve the value of this field using the web service API (thus the javascript method outlined here doesn't sound like it will work).

Any help in this would be greatly appreciated.
Photo of Shashi Kara

Shashi Kara

  • 130 Points 100 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
This can be done with reverse relationships. I can explain that in my next response.

Can we assume that countries are not duplicated in assignments for a Record?
Photo of Shashi Kara

Shashi Kara

  • 130 Points 100 badge 2x thumb
Yes, you may make that assumption!  Looking forward to the answer.
Photo of Alex Wilkinson

Alex Wilkinson

  • 1,036 Points 1k badge 2x thumb
Or, your problem may be solved in one of two ways, depending on what you are trying to accomplish:
  • If your goal is to enable users to see or report on the countries assigned to a record, then create an embedded table of assignments within the records form. Sometimes, when I've done this for users, they are more than satisfied. If they want, they can use the links in the header of the embedded table to pop it out as a separate report and add more columns.
  • Alternatively, if you are trying to extract the list of assignments for a country and use it somewhere, then you might try getting the expanded query for a report on the assignments table, and using that.
I think Mark's idea is specific to putting a concatenated text field of countries onto a record in the records table ... A good plan, to be sure, provided that it better matches your objective than the options above.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb

Reverse Relationship setup

The way to do this is with as many "reverse relationships" as you guess will be enough to cover off 99% if the situation.

One Record Job has many CR Assignments.  Make a summary field on that relationship of the Minimum of the Record ID# of CR Assignments.  Call it [Record ID# of CR Assignments 1]. 
Then do a lookup of that down to the child CR Assignments table as we will need it later.

Then do a new Relationship where one CR Assignments has many Records. (yes that sounds backwards, hence its called a Reverse Relationship)  

For the reference field (the field on the right side), use that new Summary field called [Record ID of CR Assignments 1].  Get rid of the extra fields that get created on the left side of the relationship (Add Record and that report link field).  Do a lookup from the CR Assignments down to Records and bring in the Country Name and call it Country 1.

Wonderful, the Record now knows the first CR Assignment.

Now, make a duplicate of that summary field, but in the duplicate add the extra condition that the [record ID#] is greater than the [Record ID# of CR Assignments 1].  Call it [Record ID# of Operator 2].  Do a lookup of that field back down to the Child Operator table as we will need it later.

Now, in making that duplicate, conveniently, you will find that it duplicated the reverse relationship so now do a lookup in that relationship of the Country Name  and call it [Country 2].  Wonderful again, we now have Country #2's name on the Record.

Then make another duplicate of that summary field and just keep going, each time changing the filter so that the record ID# is greater than the previously highest record ID of Operator.

In the end say you collect the names of the first 4 Countries on a Record, so then just string them together in a formula like

List ("\n",
[Country 1],
[Country 2],
[Country 3],
[Country 4])

 and put that concatenated field list on your report.

 

If you stop at say 8 countries and want to allow for the possibility of more, then you would need to create a formula field to detect that the # of CA Assignments was more than 8 (ie a summary count was > 8, and then include some text on the result text string to say

 

& IF(# CA Assignments] >8 . “.. and more....” 

Photo of Shashi Kara

Shashi Kara

  • 130 Points 100 badge 2x thumb
I have up to 192 countries that could be assigned to the case.  So in addition to the reverse relationship, I need to setup 193 fields?  192 for each country and 1 for the formula/summary field?  There's no other way to do this?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
Sorry, I should have asked what the typical maximum # of countries could be for a single Record.  Are you saying that a single record could have as many as 193 Country Assignments in practice?
Photo of Shashi Kara

Shashi Kara

  • 130 Points 100 badge 2x thumb
Yes, a single record could have up to 192 country assignments.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
Ah, sorry, then I have no native solution to offer.  This will need to be done by script.
Photo of Alex Wilkinson

Alex Wilkinson

  • 1,036 Points 1k badge 2x thumb
If you are certain that an embedded table of assigned countries won't work, here's another option ... Create a few multi-select fields and concatenate them with a formula field. If all 192 countries might be selected, you would need 10 or more multi-select fields, since you can only select 20 items from such a field. Suppose you organize them geographically: North America, South America, Eurozone, Western Europe, Southeast Asia, Pacific, etc. Then create a text formula field like this: ToText( [North America] ) & ToText( [South America] ) & ToText( [Eurozone] ) & ... etc. The result will be a semi-colon-separated list of contries in a long string. By the way, this would also eliminate the need for your join-table, if its only purpose is to establish the many-to-many relationship and not, say, to do other reporting.

Having said this, I still recommend looking very hard at using an embedded table of countries within your form. You can print the form with standard tools to get the equivalent of a printed report showing the relevant countries related to a particular record, for example, and you can email the record as well.
(Edited)
Photo of Alex Wilkinson

Alex Wilkinson

  • 1,036 Points 1k badge 2x thumb
Slight correction: to get that semicolon inserted between the regions, not just within a region, you would need this: 
List( " ; ", ToText( [North America] ), ToText( [South America] ), ToText( [Eurozone ), ... )
Photo of Shashi Kara

Shashi Kara

  • 130 Points 100 badge 2x thumb
Thanks Alex - can an embedded table be retrieved via the quickbase api?
Photo of Alex Wilkinson

Alex Wilkinson

  • 1,036 Points 1k badge 2x thumb
Yes, but you would retrieve a filtered list from the join-table (filtered for the record id# of the main record). That data is identical to what would be displayed in the embedded table within the main record.
Photo of Shashi Kara

Shashi Kara

  • 130 Points 100 badge 2x thumb
Would it be possible to use a web hook, so that every time a Record is updated, I have an API call to the assignments table that gets the list of Countries, and then write that list to a single field in the Records table?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,842 Points 50k badge 2x thumb
I believe that you will need to engage the services of a Quick Base Solution Provider to set this up for you. I do not do that kind of work myself but I can recommend some you like if you contact me via my website QuickBaseCoach.com
Photo of Shashi Kara

Shashi Kara

  • 130 Points 100 badge 2x thumb
Thanks - I do a ton of quickbase API work myself, so I can figure it out.  I'm just trying to determine whether this could be possible.  I'll investigate further.  I super appreciate your help!