Selecting the appropriate field to use in a report can be a challenge if you are just getting started. However, the following best practices can be used as a guide when determining the best field to use.
Data Groups
Data groups and sub-groups are folders which organize fields in the Page Layout Editor and Report Builder. There are many similar field labels, and interrelated objects.
We recommend the following best practices when working with data groups and sub-groups:
-
Avoid using fields in the Company Items data group unless you are building a Company report.
-
Don’t automatically choose the first field whose label matches.
-
Confirm the data group makes sense for the report goal.
Examples
-
If you are reporting on expense forecast records, avoid fields from the Expense Setup data group.
-
If you are reporting on invoice forms, avoid fields from purchase order forms.
-
-
Pull most fields from the data group for your lowest-level object.
Examples
-
If you are reporting on equipment expenses, pull equipment details from Expense Schedule data group.
-
If you are reporting on vendors for change orders, pull vendor details from the change order form.
-
Tables
A table is an object that contains related fields. An object is typically any related set of fields in Lucernex that represent a single artifact or idea—such as a contract, payment transaction, straight‐line period, percentage rent record, or schedule task. To get the appropriate data in your report, you must select a field from the correct table. A field's table name is the first part of the field's script name, following the format TableName.FieldName. For example, the Rentable Area for a contract would have the script name Contract.RentableArea.
Finding Field Names
You can view the script name from the Report Builder using two methods:
-
By clicking the Pencil icon
to the right of the field in the Fields paneThe script name appears in the upper-right corner of the Edit Item window.
To find field names in other parts of Lucernex, see our Finding Field Names in Lucernex walkthrough.
Non-Standard Tables
There are a few non-standard tables that are commonly reported on which you should be aware of:
-
Link Tables
-
LinkProjectEntityContact — the contacts on an entity
-
LinkMemberProjectEntity — the members on an entity
-
-
Virtual Tables
-
VirtualExpenseForecastPeriod — data from your expense forecast
-
VirtualSalesPeriod — sales period data
-
Reviewing Preview Data
As you add fields to your report, we recommend that you compare the results in the Preview pane or the report output with relevant on-page records. For example, when building a report on transactions, you could compare the results in the Preview pane with the records on the Transactions page of a contract.
Start by comparing data from individual records or individual entities, and slowly increase to compare multiple records and entities. Your goal is to validate that there are no errors caused by cross-table interactions.
Foreign Keys and Shine-Through Fields
For deeper reports, you may want to use Foreign Key fields and Shine-Through fields. A Foreign Key is a field that links one type of record—or object—to another. Many records in Lucernex have a hierarchical structure—for example, the Contract database table is the child of the Location database table, and it is the parent of the Expense Setup database table. These relationships can be one to one or one to many.
A Shine-Through is a field associated with the linked Foreign Key field. For example, we could use the Location Type shine-through field associated with the Location foreign key field on the Contract table.
Foreign Key fields appear as groups within a data sub-group, while Shine-Through fields are the fields contained within these groups. In the screen shot below, the foreign key field is Amendment (labeled #1), and the shine-through fields appear as children below it (labeled #2).
You can also add a shine-through field by following the Filter a Field by a Foreign Key procedures.
