When a database is initially provisioned, the standard Organizations dataset is configured to display the Location information on the Lookup page – these values are preset/pre-loaded. However, for newly added/updated Organizations records, this information will not be automatically populated – see example below.
To ensure that a Location is populated for all Organizations records moving forward (including new additions to your database), you will need to create an Organizations-scoped Index/Header rule to do so.
Create the Rule
1. Create an Index/Header Type rule using the Configurable Joins base of Organizations – returning one row per Organization record.
2. Filters - Leave the Filters section empty so that this rule will affect all Organizations records.
3. Action - Select an Action of Replace Values from Formula.
4. Add an overall Join to Address by Rank Overall, with Rank 1 specified – so that only the Overall Rank 1 Address will be returned when referencing that Join. This Knowledge Base article details the various ways in which objects within Slate are ranked. This information is beneficial when determining which specific object you would like to return within your queries.
5. Add a Subquery Export to create the header using the desired values. Be sure to give this export a computer-friendly name (i.e., all lowercase, no spaces, or special characters) for ease of use within the formula.
This Subquery Export displays in City, State.
The Nested Subquery Exports ensure that the correct value is returned depending on where the Organizations record is located.
These values are separated by a " , " resulting in [City], [State/Country] (depending on the record’s address).
6. The Formula of the rule will then reference the Subquery Export by adding an '@' followed by the name of the export, that value can be pulled into the formula.
Retroactive Refresh - For this rule to run on the existing records within your database, an update will need to be made to those records to add them to the rules queue. Therefore, you will want to perform a Retroactive Refresh on your Organizations records to assign the desired values to the index. Depending on the number of records that exist within your database, we recommend performing this refresh at a time when the number of users in the system will be at a minimum.
Click here for more information on how to build queries using Configurable Joins.