The Lookup Tables feature provides a centralized data lookup system for administrators to create and manage tables at the company and project level. By referencing these tables in Field Expressions with the Lookup() function, customers can simplify complex expressions and improve data consistency.

To take the Lookup Tables course, login to Stratus Academy and and locate the course ADM-524 Tables.

The examples below illustrate using the Lookup Table and Lookup function in combination with package and part properties to calculate and display data.

Open

In this example, labor rates are used in conjunction with package and/or part properties to drive labor estimating tables.

1. Create a CSV file to include the Lookup Table columns and data.

   Open

   

    

2. Under Admin > Company > Tables, either click the Create Table option to create a new table or click the Edit button associated with an existing table.

   Open

   

    

3. Click the Upload CSV button then click Browse to locate the file.

4. Click the Update button. The CSV file will import to the Lookup Table.

   Open

   

    

5. Create or edit a Field Expression to include the Lookup function.

   1. Note: Property Names (e.g., OEM, Size, Material, Material Gauge, Product Name) must be included in curly brackets {}.

      Open

      

       

6. Add the Field to a new or existing report.

   Open

   

    

7. Run the report. The report automatically pulls the right data or calculation using the Lookup Table.

   Open

   

Lookup Table - ProjectInformation

Open

Stratus.Fields with Lookup()

Open

Report and Template (Stratus Sheet)

Create a Lookup Table and Report Fields, add the fields to a Report Template and the Stratus Sheet displays the Lookup Table content.

Open

If a part property contains a value that is inconsistently named in the Fabrication Database, ensure that Stratus report data uses the preferred spelling. Create a Part Property Lookup Table where the Spelling column includes all of the possible spellings (e.g., Cooper, CU, cu, Cu, Carbon Steel, CarbonSteel, CS, Schedule 40, Sched 40). Another column called the Correct Spelling lists the preferred spelling in each row. Create the Field to reference the Lookup Table. Then, change any reports that reference the part property to the Part Property Spelling Lookup Table.

Any Project Roles that will need access to the Tables tab at the Company or Project level will need the Tables permission.

Open

Open

A new Lookup Table can be created and updated manually.

To create a new table:

1. Click the Create Table button.

2. Table Name (Required) - Enter a Table Name.

3. Note (Optional) - Enter a note about the table as needed.

4. Clone CSV data to project tables when first created - This checkbox impacts how tables are created at the project level. See the Project Tables (Admin) article for more information or the Admin > Projects > Tables summary below.

   1. Checked - When checked, headers and row data values from the company level will be copied into newly created Project Tables at the project level. This helps ensure consistency and reduces manual data entry errors during project setup.

   2. Unchecked - When unchecked, only the header values from the company level will be copied into newly created Project Tables at the project level.

5. Data Input Method - Click the Manual Entry (Default) button.

   Open

   

    

6. Headers - Enter column headers in display order, separated by commas. Headers display in the Table Data section as they are entered. Headers must exist in Stratus before uploading a CSV to update row data.

   Open

   

    

7. Add Row - Click the Add Row button to add cell rows. Cell content can be entered now or later in the Edit dialog.

   Open

   
   Open

   

    

8. Add Column - To add a column:

   1. Click the Add Column button to add a column. The column will be added after the last column. Edit the column header and enter cell data as needed, and then click Update.

      Open

      

       

9. Clear All Data - Click the Clear All Data button to clear all row data. The column header values will remain.

   Open

   

    

10. Delete Column - A delete button displays below each column. To remove a column, click the corresponding Delete button. The example shows Column L being deleted. Click Update when done.

    Open

    

     

11. Delete Row - A delete button displays in the Actions column for each row. Click the Delete button to delete any row. In this example, row 1 was deleted leaving row 2. Click Update when done.

    Open

    

    
    
    

Lookup Tables can be created and updated by uploading CSV files sourced from Stratus Big Data exports, existing spreadsheets, or downloaded Stratus reports. For an example using a downloaded Stratus report, refer to the ADM-524 Tables course in the Stratus Academy.

To create a new table:

1. Click the Create Table button.

2. Table Name (Required) - Enter a Table Name.

3. Note (Optional) - Enter a note about the table as needed.

4. Clone CSV data to project tables when first created - This checkbox impacts how tables are created at the project level. See the Project Tables (Admin) article for more information or the Admin > Projects > Tables summary below.

   1. Checked - When checked, headers and row data values from the company level will be copied into newly created Project Tables at the project level. This helps ensure consistency and reduces manual data entry errors during project setup.

5. Data Input Method - Click the Upload CSV button.

   1. Note: Create a CSV file either by downloading and editing an existing Lookup Table or create the CSV file from scratch by including column headers and row data.

6. Click the CSV File Browse button.

7. Select the CSV file and click Open.

8. The Lookup Table rows will populate from the CSV file.

9. Click the Create button.

   Open

   

    

10. The Lookup Table will display the newly created table.

    Open

    

Once Lookup Tables are created they display in the Lookup Tables data table.

Open

1. Search - Enter characters to search for a Lookup Table.

2. Name - The Name of the Lookup Table.

3. Notes - Optional Notes entered for the Lookup Table.

4. Columns - The number of Columns included in the Lookup Table.

5. Rows - The number of Rows included in the Lookup Table.

6. References - Click the References number to display what is referencing this Table. In this example, the popup specifies that “The following Field Expression … This helps the Admin understand where and how the Table is being used in Stratus.

7. Modified - The date/time the Lookup Table was last modified.

8. Actions

   1. View - Click the View button to see the Lookup Table contents.

      Open

      

       

   2. Edit - To edit an existing table:

      1. Under Actions, click the Edit button.

      2. Click the Upload CSV button.

      3. Click the CSV File Browse button.

      4. Select the CSV file and click Open.
         
         

   3. Column Operations

      Open

      

       

      1. Add Column - To Add Column:

         1. Column Name - Enter the new Column Name.

         2. Insert Position - Select the Insert Position of the new column.

         3. Default Value - Enter a default value, if any, that will populate all rows in the new column.

         4. Click the Add Column button. Note: All project tables that reference this company table will be automatically updated to maintain data integrity.

            Open

            

             

      2. Remove Column - To Remove Column:

         1. Column to Remove - Select the Column to Remove.

         2. Click the Remove Column button. Note: All project tables that reference this company table will be automatically updated to maintain data integrity.

            Open

            

             

      3. Rename Column - To Rename a Column:

         1. Column to Rename - Select the Column to Rename.

         2. New Column Name - Enter a New Column Name.

         3. Click the Rename Column button. Note: All project tables that reference this company table will be automatically updated to maintain data integrity.

            Open

            

             

      4. Reorder Columns - To Reorder Columns:

         1. Click the up or down arrow buttons in the Actions column.

         2. When done click the Apply New Order button. Note: All project tables that reference this company table will be automatically updated to maintain data integrity.

            Open

            

             

   4. Download CSV - Downloads the data table CSV to the local computer.

   5. Delete - Prompt to delete the table.

The purpose of Tables at the Project Level is to override the Company Table for the selected Project. See the Project Tables (Admin) article for more information or the summary below.

Note:

1. Headers are inherited from the Company level reference Table.

2. A Table configured at the Project level overrides the Table configured at the Company level only for the selected project.

To override a Company Table for a Project:

1. Go to Admin > Projects.

2. Select the Project.

3. Click the Tables tab.

   Open

   

    

4. Click the Create Table button. The Create Table dialog will displays.

   Open

   

    

5. Company Table to Override - Select a Company level Table.

   Open

   

6. With the company table selected, select the Data Input Method.

   1. Manual - See the Create Table (Manual) section for more information.

   2. Upload CSV - See the Create Table (Upload CSV) section for more information.

See the Lookup() Function for more information.

The Lookup() function retrieves data dynamically from part a customer defined Lookup Table and is applied in a Stratus Field Expression. See the Fields (Admin) article for more information.

• tableName: Name of the lookup table

• returnColumn: Column name to return the value from

• columnName: Column to search in

• operator: Comparison operator (=, !=, >, <, >=, <=, LIKE, ~, IN, ?)

• value: Value to match against, typically should be variable in { } curly brackets, like {Diameter}

• defaultValue: Value to return if no match is found

The Lookup function supports a comprehensive set of comparison operators for flexible data matching:

• = - Standard equality (e.g., Material=Steel)

• == - Alternative equality syntax (e.g., Grade==A36)

• != - Not equal (e.g., Status!=Inactive)

• <> - Alternative not equal syntax (e.g., Type<>Old)

• > - Greater than (e.g., Price>100)

• < - Less than (e.g., Quantity<50)

• >= - Greater than or equal (e.g., Date>=2023-01-01)

• <= - Less than or equal (e.g., Name<=Z)

• LIKE - Pattern matching with wildcards (e.g., Description LIKE %Steel%)

• ~ - Alternative pattern matching syntax (e.g., Name~%Smith%)

• IN - In or Contains (e.g., “Description" IN "Sample Description")

• ? - Alternative “in” syntax (e.g., Name~%Smith%)

• % - Wildcard for any sequence of characters

• _ - Wildcard for any single character

• Case-insensitive matching

• Examples:

  • Name LIKE %Smith% - Finds "Smith", "Smithson", "Blacksmith"

  • Code LIKE A_ - Finds "A1", "AB", "AX" (any 2-character code starting with A)

  • Description LIKE %Steel%Pipe% - Finds descriptions containing both "Steel" and "Pipe"

The Tables tag include GET, POST, PUT, and DELETE endpoints to support the Tables feature.

Open