Skip to main content
Question

List all Contacts being used in form

  • June 7, 2020
  • 0 replies
  • 28 views
I am trying to have a drop down in the main form that lists all contacts currently in use in the form.

My relationships are as follows:

Clients < Work Orders
Clients < Contacts
Contacts < Work Orders (x9)

I have a Client drop down in the Work Order form.
I have 9 relationships established between the Contacts table and Work Orders table due to the various types of contacts, i,e supervisor, engineer, specialist, etc.
I have 9 contact drop downs in the Work Order form, one for each contact type. They only show contacts that have a matching type. There is a drop down for Supervisor, one for Engineer, one for Specialist, etc etc etc.
The 9 contact drop downs are conditionally based upon the client selected.
Not all dropdowns need to be used for the process to move forward, only one at the very least.
Also, each contact type can have various contacts for the same client.
Example: For the same client, there could be 3 Supervisors, 4 Engineers, 2 Specialists, etc.
So when I select the client and then the Supervisor dropdown, it shows me only contacts associated with that client that are supervisors. Same goes for all the other contact type dropdowns.
This allows me to select which contact for that type, associated with the Work Order in question.

Later on in the process, I need a dropdown that will list all of the contacts previously selected/input for that Work Order. The form and its contents will be saved/updated prior to needing to use the new drop down.
For example, if there is a contact selected/input for slots 1, 4, 7, & 8. I need all those names listed as available/selectable in the new drop down, regardless of type. I don't want all contacts for that client listed, only the ones previously selected for that specific work order.

I thought about creating an additional relationship between the contacts and work order but like this: Work Orders < Contacts. I figured that this would associate the Work Order ID (unique ID#) to the contacts. Then I could have a report built with a filter that only shows contacts associated with the current Work Order ID (I have done something similar for another relationship between other tables and it works).

I could take that report and associated with to a field dropdown in the main Work Order form, thus listing all contacts already used in the work order.

Except that it didn't work and I am not sure where I went wrong, if my thought process is incorrect, if I am missing something, etc.

I think that perhaps part of the issue might be the Contacts < Work Order relationships might not be correctly associating the Work Order ID# with the corresponding contact, and thus populating into the other relationship and down to the report.

Any thoughts anyone has, ideas, suggestions, etc., is appreciated.

------------------------------
Andrew
andrew.fry25@gmail.com
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
Interesting problem.

I would do this by creating a table called User Focus with the Key field set to be a userid field.  That table would have a formula checkbox field with a value of true and would be related to the Work Order Table with a formula User field called [Current User] with the formula of User().  I would then look up that User Exists Checkbox field.  The User Focus table will also need to have a numeric field called [Record ID# of Focus Work Order]

I would then have 9 combined text summary fields to float up the email fields to the User Focus table.  I strongly suspect that it won't let you do that directly so would will need to have 9 formula fields to convert the email field types to a text field type.  

I would then combine the 9 combined text email values into a regular text field type string called [All emails in text format] 

Then you will need another relationship form User focus down to Contacts again based on a formula user reference field of [Current User].  Then lookup [All emails in text format]  down to Contacts.

Now, lastly make a report to drive the dropdown list list on the work order form where the contact is contained in that string.  

The Work Order form will need a button to put the Work Order in Focus for the current user. ie the lookup of the 

Edit or Create a User Record for the Current User (remember to set permissions in the User focus table)
Set Key Field
User Exists - true!

Remember to set permissions

var text AddUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_AddRecord"
& "&_fid_6=" & ToText(User())
& "&_fid_7=" & ToText([Record ID#]);

var text EditUser = URLRoot() & "db/" & [_DBID_USER_FOCUS] & "?act=API_EditRecord"
& "&key=" & ToText(User())
& "&_fid_7=" & ToText([Record ID#]);

var text DisplayRecord = URLRoot() & "db/" & dbid() & "?a=dr&rid=" &  [Record ID#];

If([Current User - User Exists?],
$EditUser& "&rdr=" & URLEncode($DisplayRecord),
$AddUser& "&rdr=" & URLEncode($DisplayRecord))

Lastly you will need a form rule to force the user to put the Work Order in focus in order to see the edit field for the dropdown for that 10th contact field. ie where the lookup of [Record ID# of Focus Work Order] is equal to [Record ID#].  That comparison should be done in a formula checkbox field and then used in the form rule.


------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Terms & ConditionsAccessibility statement