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...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendInteresting 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
------------------------------
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
------------------------------
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 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
------------------------------
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__YouAndrew,
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
------------------------------