Forum Discussion

AndrewFry's avatar
AndrewFry
Qrew Assistant Captain
5 years ago

List all Contacts being used in form

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
------------------------------
Forms
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    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
    ------------------------------
    • AndrewFry's avatar
      AndrewFry
      Qrew Assistant Captain
      5 years ago
      Hey there Mark. Thanks for the input, I appreciate it.

      After reading your post, and trying to implement it as best I can, I am not sure it actually does what I am hoping to achieve. It could be that I didn't follow your example well enough, or perhaps I wasn't very clear in my description. Looking back, my original post seems to be a bit too detailed. I apologize.

      So basically in my main form, I need a record picker to display only the options previously selected from other record pickers in the same form.

      Example: (I am going to use different table names than from above for ease of reference) The relationship for MENUS and FOODS is as follows: FOODS < MENUS. I have 3 record pickers in my main form in the MENUS table, each based off of the FOODS table. All three are filtered (via FOODS reports) to show me only options based upon  food types: Fruits, Vegetables, Grains. Let's say I select Apple for Fruits, Carrot for Vegetables, and nothing for Grains. 

      The main form will then be saved. At some point later in time, I will need to go to a different record picker in the main form of the MENUS table, let's call it "Options". This record picker needs to show me as various options, the items previously selected in the other record pickers. Also, it should only show me the options selected for that related Parent Record. In the example above, it would show me Apple, and Carrot. In essence it is capturing the various selections in the other record pickers and presenting them all as options in a different single record picker.

      If I were to go back and select a grain, say Oatmeal, then the Options record picker would show me all three options, allowing me to select one of them for that record picker. It should not show me options for those fields, selected in a different MENUS record, only the related parent record.

      Perhaps the Options record picker really doesn't need to be a record picker, as a record picker is based off of the related records from a table relationship. Maybe the best way is to have a formula field of some sorts that captures the various selections from the other record picker fields, and presents them to be selectable. (????) Only one of the various options needs to be selectable .......

      What I have tried has not worked thus far. Here is my most recent attempt (going on 10 hours of various things tried):
      • I created a new table.
      • I related that table (using my example from above) as follows: MENUS < FOOD OPTIONS
      • I select an option from the Fruits record picker, Vegetables record picker, and Grains record picker.
      • I have various automations setup so that if a record is added/modified, based on the Fruits/Vegetables/Grains record pickers, it creates a new child record in the FOOD OPTIONS table, with each new child record in the FOOD OPTIONS table being related to corresponding MENUS table parent record.
      • However, this does not allow me to generate a filterable report off of the FOOD OPTIONS table, for a record picker field in the MENUS form.
      • I have tried a number of other various options, all getting so close, but then right at the critical point, it not working.

      I am pretty much at my wits end here. This is one of the final pieces of this project that I need to complete. To be stalled out right at the end is killing me!!! hahahahaha

      Any insight anyone can offer, is greatly appreciated.

      Sincerely,
      Spydey, aka Losing my hair more and more everyday ....

      ------------------------------
      Andrew
      andrew.fry25@gmail.com
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        Andrew,
        I think that I would have to work one on one with you to get this working.

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