Catalog Item | Retrieve Attribute from Different Table

[claudio_palmeri]

Hi Community!

I have a Catalog Item where the first variable is 'Requested For'. I am able to retrieve the User record without issues and then populate a bunch of 'Single Line Text' where the values are auto-populated based on the Requested For. Easy!

I now have a requirement to populate other 'Single Line Text' variables with strings from other (not User) tables. My thinking is something along the lines of:

DISPLAY tableABC.attributeDEF WHERE tableABC.email = requested_for.email

How can I make this happen?

[Rajesh Chopade1 · Accepted solution]

hi @claudio_palmeri 

You can definitely achieve this by querying records from other tables (not just the User table) based on values such as the email address, and then populating the Single Line Text variables in the Catalog Item.

Use a Script Include or a Catalog Client Script to retrieve values from the tableABC based on a condition (like matching the EMAIL field with the Requested for email).

function onLoad() {
    // Get the email of the Requested For user
    var requestedForEmail = g_form.getValue('requested_for.email');  // Ensure you're getting the email value correctly

    if (requestedForEmail) {
        // Query tableABC using the requestedForEmail
        var gr = new GlideRecord('tableABC');
        gr.addQuery('email', requestedForEmail);  // Matching email address
        gr.query();

        // Check if a record exists
        if (gr.next()) {
            // Populate the Single Line Text variables with data from tableABC
            g_form.setValue('variable_1', gr.attributeDEF); // Replace 'variable_1' with the actual variable name
            g_form.setValue('variable_2', gr.attributeGHI); // Replace 'variable_2' with the actual variable name
            // Add more variables as needed, mapping the fields from tableABC to the variables in the form
        } else {
            // Handle the case when no record is found
            g_form.setValue('variable_1', 'No record found');
            g_form.setValue('variable_2', 'No record found');
        }
    }
}

If you want to make this functionality more reusable, especially if you plan on performing similar queries for other catalog items or processes, consider using a Script Include.

I hope my answer helps you to resolve your issue, if yes please mark my answer helpful and correct.

thank you

Rajesh

[claudio_palmeri]

Thank you, @Rajesh Chopade1! I will try your solution shortly.

Looking at the script, wouldn't it make more sense to be onChange rather than onLoad?

[Rajesh Chopade1]

hi @claudio_palmeri 

Yes, you're absolutely right! Whether you use onLoad or onChange depends on the user experience you're aiming for.

[Ankur Bawiskar]

@claudio_palmeri 

Sorry your requirement is not clear.

Based on what value you need to populate other Single line variable?

Please share some screenshots etc

If my response helped please mark it correct and close the thread so that it benefits future readers.