Reference Qualifier based on previous field

[gm01]

I am trying to figure out how to create an encoded query string for a reference qualifier or see if I have to possibly script this.   I have a request form which the first variable which is a lookup select references the sys_user table based on the user_name and pulls a custom field of test_uuid as the lookup value field.   The name of that variable is vm_user.   Now the next selection is a variable called test_proj which references another table that has values (in a column called proj) associated with that value of test_uuid.   I want to make sure that when the user is selected that only the values in the table that match test_uuid are returned.   Can this be done in a encoded query where I have the reference lookup the values for proj and only show proj values that match the test_uuid value or will this require a script include?

[Erik Stolberg]

Sorry I guess looking back on the OP, you didn't say it was a reference field. Sys_ID is only required if the field is a reference.

If your lookup value for test_uuid pulls back "Oranges" and you want to use that value to look up your proj field, that should work. Again, just query your table to get some sample data, and grab the encoded query to work with. It should work.

[mikeinsley]

Hi,

I'm having a similar issue with two Lookup Select Box fields:

Catalog Variable 1 - u_job_description

Lookup from table - u_business_units

Lookup Field Value - sys_id

Lookup label field(s) - name

Ref Qual - u_active=true^u_company=2363dd45374b310045e22ea843990e50^u_domain=3f8410520f2d79009850ecd692050ed1^u_department=baf5744c6f0b1240b75efbf7eb3ee433^EQ

Catalog Variable 2 - u_role

Lookup from table - u_static_data

Lookup Field Value - sys_id

Lookup label field(s) - u_value

Ref Qual - javascript:'u_function_type='+current.variables.u_job_description;

For example, a simplified view of the tables and fields would be:

u_business_units

Active	Name	Company	Department	Domain
TRUE	Job1	Company1	Dept1	Domain1
TRUE	Job2	Company1	Dept1	Domain1

u_static_data

Active	Topic	Function Type	Value	Company
TRUE	Role Profile	Job1	Role1	Company1
TRUE	Role Profile	Job1	Role2	Company1
TRUE	Role Profile	Job1	Role3	Company1
TRUE	Role Profile	Job2	Role4	Company1
TRUE	Role Profile	Job2	Role5	Company1
TRUE	Role Profile	Job2	Role6	Company1

Variable 1 displays the contents of the Name field - that works

Variable 2 should reference variable one's selection against the Function Type field and return the Values related to it. (i.e Job1 is selected in Variable1, Role 1, Role2, and Role2 are the filtered options visible in Variable2) However all data in the Values field are being returned unfiltered.

For info, u_function_type is the field where the corresponding values to u_buisiness_units.name are saved. Neither field is a reference to another table, all are String fields.

Thanks

Mike

[Erik Stolberg]

Your top catalog variable is returning a sys_id for the record (say, row 2 in your same table data) based on the value field being sys_id.

This means that your Ref Qual on catalog variable #2 is showing up like this:

u_function_type=4ac410320d2a71209451eab692050c1a //whatever the sys_id is for catalog variable 1

You mentioned that your u_static_data.u_function_type isn't a reference field... I think this is where the issue is. Your second Ref Qual needs to use the Name value to match the string value in your second table (it's not a reference to the other table, thus the sys_id doesn't mean anything to it).

An easy way to test if this is the case without making any other changes is to modify your second Ref Qual to a static value and see if the list is filtered as expected... something like:

javascript:'u_function_type=Job1';

[Lisa Silvaroli]

I am trying to achieve this same thing but nothing I've tried is actually populating my reference field. I am either getting all or nothing...  

Here is my info:

Basic filter on building:

sys_id=80fcde246f869100f1363ee9ea3ee42c

My Catalog Item Variable for District (cslo_district😞

Lookup from table = core_company

Lookup value field = Not sure?

Lookup label field(s) = name

Reference qual = customer=true^EQ

My Catalog Item Variable for Building (location):

Lookup from table = cmn_location

Lookup value field =

Lookup label field(s) = name

Reference qual = javascript:'sys_id='+current.variables.cslo_district;

I'm not sure if I maybe did the first query incorrectly? Or something with the tables I am pulling from?

Any help would be greatly apprcitated!!!

[Erik Stolberg]

So you would like to have a user pick a company ("District") and have the "Building" field dynamically update to show all buildings tied to that "District"? Try the following:

My Catalog Item Variable for District (cslo_district):
Lookup from table = core_company
Lookup value field = sys_id
Lookup label field(s) = name
Reference qual = customer=true^EQ

My Catalog Item Variable for Building (location):
Lookup from table = cmn_location
Lookup value field = sys_id
Lookup label field(s) = name
Reference qual = javascript:'company='+current.variables.cslo_district;