Purpose & Scope:
Spitfire offers a Lookup Editor utility for maintaining and creating Lookups for use in sfPMS. This Lookup Editor requires access to your SQL Server and the Spitfire database. It is assumed that users of the Lookup Editor have a good understanding of SQL Databases and SQL commands.
Procedure:
- Install http://www.spitfiremanagement.com/clickOnce/LookupEditor.
- Click sfLookupEditor.application. When run, it will ask for a Windows authentication-backed connection to a database. We strongly recommend using a test database.
Lookups
- FILE | Copy a lookup to a new name (that can be customized at the site). Look for a new row in the grid starting new-…
- FILE | Export a lookup makes it easy to import a lookup to another database.
- Tools | Test Query can test your lookup.
- Tools | View Log is used to “grab” update commands so they can be recorded in ICTool / Site Wrapup.
Edit a Lookup
IMPORTANT: if you edit/tweak a SPITFIRE lookup you MUST copy the TSQL to update the lookup to ICTool’s “site wrapup” script or a Data Layer Extension. Without this step Spitfire will overwrite the lookup definition in the future. Custom lookups can be edited in place and occasionally “exported” for saving a backup outside of the database.
- Name — (the name of Spitfire lookups should *never* be changed); by convention, begin custom lookup names with a Z
- Product – should always be SPM
- DataSet – either blank or SOL (blank runs the lookup in the spitfire db, SOL runs the query in the DSL application db)
- SEQ – not used, leave as zero
- Caption – what the user sees on top of the lookup window. For sanity, should be unique
- DataQuery – TSQL data gathering command. Use Pencil tool icon to open an editing window
- Active – uncheck to effectively hide the lookup
- Preload – in theory, uncheck on a long running query so that the filters are presented with an empty result set
- Primary Output – the GUID of a row in the Lookup Result (seldom changed)
Fields
- Sequence – controls the order of the columns.
- Field Query – the SQL field ID
- Alias – the query alias assigned to the field (as xxx); also helps with filter UI (see below)
- Label – the header of the column in the lookup grid. By convention we use PICK when the first column is a GUID
- Show –
- P for primary (always shown)
- S for secondary (shown in expanded mode)
- H for hidden (never shown)
- Format – blank is fine, but can help to format the column. Examples:
- :@C_ProjectMask – for masking a project ID
- :@C_TaskMask – for masking a cost code
- F4 – for 4 decimal places, C2 for currency with 2 decimal places, etc
- M/d/yyyy – for a date format… Many similar date formats are possible, such as M/MM/MMM, d/dd/ddd, yy/yyyy
- Grid Column Type – blank is fine
- DBCheckMark is a nicer presentation of true/false
- Result GUID
- When you click in this field you get a drop down of Lookup Results. Tab out and the GUID is stored here.
- Ordering – generally B for allowed to sort on this column by clicking on it
- A for presort ascending
- P-Mode – Predicate (Filter) Mode
- N for Exclude (not a filter)
- P for Primary filter – always displayed
- D for Dynamic – displayed when filter list is expanded
- R for Required — seldom used, currently only for date range. When not specified by the user, the system will supply a default value for this filter parameter.
- I for Input – a static value passed into the lookup. Seldom used. Must be combined with @pD1…@pD4 in PName
- Predicate – the TSQL used to build the left side of the WHERE predicate condition. If in tableAlias.[fieldAlias] format, be sure to include the square brackets
- Operator–
- B for BETWEEN
- L for LIKE, ending % implied if no % entered by user; LIKE operator will be added unless the LIKE keyword already appears in the Predicate.
- K for LIKE, like L but adds implied leading %
- = for =
- > for >=
- P for included in predicate. Often uses for EXISTS() based predicates
- PName – seldom changed, must be unique. If using “input” P-Mode, change to @pD1 to @pD4 (the four possible inputs)
- Param Label – The label displayed on the filter in the lookup window
- P-Default – place a value here and it is used as a hidden initial filter for the query. The user can enter a filter of their own if this filter is visible
- Active – uncheck to remove/hide this column Very similar to setting Show=H and P-Mode=N
When a lookup field has a P-Mode other than N, it will be rendered using the filter UI. Most filters work with a standard text box entry, but if the alias name of the lookup field matches on of the following an enhanced filter is used:
- ExternalPrimary – drop down based on Code List Set name ‘PrimarySource‘
- IsPrimary – drop down based on Code List Set name ‘Boolean‘ (with OnAdd flag set)
- TeamFlag – Hidden unless project is known, drop down based on Code List Set name ‘Boolean‘ (with OnAdd flag set)
- IsClosed – drop down based on Code List Set name ‘Boolean‘ (with OnAdd flag set)
- IsOpen – drop down based on Code List Set name ‘Boolean‘ (with OnAdd flag set)
- Rating – text box with positive integer validation applied
- RegionID – nested lookup using RegionID lookup
- VendorUnion – drop down based on Doc Type Code Set name ‘VendorLabor‘
- VendorMinority – drop down based on Doc Type Code Set name ‘VendorClassif‘
Lookup Query
Your query can use the following parameters:
- @pv – The “current” value, if any
- @pD1 … @pD4 – One to four input values
- @pUID – the GUID of the current user
- @pProject – the context of the current project, if any
Note: All the parameters are based on context and UI Configuration. Specifically, the UI Configuration “DependsOn” attribute must typically be specified so that @pD1 – 4 have values in a specific context! For examples, see KBA-01336
Lookup Results
- Lookup results are used to “test” if a value exists and return some sort of description
- Heavily used by the system and lookups
- Much less likely to be configured at this time
- The system can pass the test value (@PV) and one to four option values upon which the test value depends (@pD1, @pD2, @pD3, @pD4)
- The system caches these results indexes by @PV and (@pD1, @pD2, @pD3, @pD4)
- These are scalar queries: they must return a single row, single column
Additional Comments
Installed from http://www.spitfiremanagement.com/clickOnce/LookupEditor
The Lookup Editor is provided as-is and is not a supported feature in sfPMS. For assistance creating and troubleshooting lookups, please check with your implementer. After saving changes, you can use the lookup editor log file to gather commands for your ICTool Site Wrapup script.
Before testing changes, you must use the Clear Lookup Cache button on the View | Advanced | Diagnostic Tools page.
KBA-01404; Last updated: November 28, 2022 at 16:54 pm;
Keywords: customizing lookups