Editing Table Formats and Aggregations in Specify 7

Editing Table Formats and Aggregation

Table formats determine what fields to display and the order of those fields for a specific table in query results, exports, and query combo boxes. For example, in the Agent table if you want Agents’ names to show up as ‘last name, first name’.

Table aggregation determines how multiple tables will be consolidated in query results and table formats. For example, a collecting event may have more than one collector, so aggregating the collector table would establish how the collectors would appear.


Accessing Table Formats and Aggregations

There are two ways to get to table formats and table aggregations

  1. Navigate to the User Tools menu, then go to Schema Config

    • Next, choose whatever table you want to create the table format/aggregation for and then you can select from the picklist or click the :pencil_: if there is already one made or click on the :plus_: by either table format or table aggregation to make a new one
  2. Navigate to the User Tools menu, then go to App Resources

    • From here, you want to make sure you can see App Resources (by clicking the big App Resources button at the top of the page), then navigate to the Record Formatters (DataObjFormatters) file that applies to your current discipline or collection. In this case, I am editing the file under the Ichthyology collection.

:bulb: Note: If you do not have this file then you will have to add it to any rank in the App Resource Hierarchy. At your chosen level, click :plus_: Add Resource:gear_: App ResourceRecord Formatters

Table Formats

Conditional Formatter

At the top of the form, there is a checkbox for a Conditional Formatter. What this does is allow for a field to show up in only some conditions and not all. First, you will choose what field you want the table format to be set to and then you can select the conditions.

In this example, the conditional format is current so the conditions are true or false. At the end under the preview, you can see that if the determination is current it has the fields and separators that are under the ‘true’ condition and if it is not current it has the field that is under the ‘false’ condition.

Customization

Let’s go through an example using the Address format:

Field

If you click on Add Field you will add another field with a blank separator box and a field picklist. You’ll want to select the field(s) you will want to show up for the table. If you have more than one field you can use the arrows on the right to move any of these fields up or down to reorder them and you can delete any unwanted fields by clicking :trash_:.

Separator

Under the separator section you can also choose how you want the fields to be separated, this can be anything you want. In the screenshot above you can see there’s a comma for City and State whereas Address2 and Postal Code have a space.

Field Formatters

Below the fields on the right is a Customize Field Formats checkbox, when checked you can choose from a picklist if you want a customized format for any of the fields.

Preview

Once you have all the fields you want you can scroll down to see what a preview looks like. From there you can decide if you want to change anything based on how it looks. You can also click on the eyeball to see the specific record or Search where you can use the query builder to search for records.

Table Aggregation

In the aggregation section, you have all of the table aggregations that you can apply to a table/field in queries and on forms.

Customization

In the table aggregation, you can customize the separator, suffix, limit, table format, and how you want it to be sorted.

Separator

The separator here works the same as it does in table formats. You can add anything from a comma to a space, or you could put a word if you want.

Suffix

The suffix is whatever you want to appear at the end of the text string.

Limit

The limit is how many table records you want to be returned. If you create a new aggregation the limit will be set to zero which means the maximum amount of results available will be returned but you can change this number to whatever you want.

Table Format

The Table format section is for when you have multiple table formats that could apply to the aggregation. For example, if you have two different determination table formats you can select which table format you want the aggregation to apply to.

Sort Field

This is just how you want the table records to be sorted. There’s a picklist of the different fields you can select and you can scroll down to see different tables and hidden fields.
In this case, I sorted the determination by current and you can see the current taxon at the end.

Example

For example, let’s consider the Collector table:
In the Table Format section the Collector is defined by Agent with ‘last name, first name’. There can be multiple collectors for one collection object so under Aggregation you can define how you want to separate each collector and if you want any suffix at the end.

In this video, I created a collecting event that had 4 collectors. The original aggregation had no separator between each of the collectors, so I went back and added a | for the separator. This way each agent will show up as ‘last name, first name’ and have the | at the end to separate them so it is very clear whose name is whose.

Hi @emenslin - In 7.9.3 I created a Collector formatted view where (primary) appeared as a suffix after a Collector’s name, when they have isPrimary ticked.
I was doing this with the following xml:

<format name="Collector" title="Collector" class="edu.ku.brc.specify.datamodel.Collector" default="true">
		<switch single="false" field="isPrimary">
			<fields value="true">
				<field sep=" ">agent.text3</field>
				<field type="boolean" format=" " sep=" (primary)">isPrimary</field>
			</fields>
			<fields value="false">
				<field>agent.text3</field>
			</fields>
		</switch>
	</format>

I can’t see how I would do this with the new visual interface in 7.9.4. We have deployed 7.9.4 in a test instance, and I can apply the xml above via the xml editor, and it works as it does in 7.9.3. But is there a way to achieve the same result using the visual editor?

The only option I think would be to keep the conditional format, but remove the isPrimary field from the ‘true’ option config, and then have a '(primary) ’ separator for text3, which would put the '(primary) ’ in front of the text3 name. Not ideal.

Think the xml would be:

<format name="Collector" title="Collector" class="edu.ku.brc.specify.datamodel.Collector" default="true">
		<switch single="false" field="isPrimary">
			<fields value="true">
				<field sep="(primary) ">agent.text3</field>				
			</fields>
			<fields value="false">
				<field>agent.text3</field>
			</fields>
		</switch>
	</format>

I tried to select something from the ‘Custom Field Format’ and it appeared in the xml as ‘uifieldformatter’ rather than ‘format’ like in my original xml. And editing the xml to be uifieldformatter=" " doesn’t do anything.

Is there a way to add to the ‘Custom Field Format’ drop down, so you can make a field appear either as an empty string (and use the separator field for the actual display value) or appear as a particular string you can specify?

Or is it possible to add a suffix to a formatter? Similar to how you can add one to an aggregation.

I can continue to override the visual editor with my specific xml tweaks, but I worry it may get overridden itself if the formatter is tweaked by someone else using the visual editor.

Thanks,
Zoe

Hi Zoe, based on the xml you provided this should be how the visual editor should look to achieve the same results you had in 7.9.3.


If this does not give you the results you were looking for then we can discuss potential alternate solutions to get the desired result!

Thanks,
Elizabeth

Thanks @emenslin for your response,
I should have included screenshots of the actual output.
With my specific xml tweaks, the collector formatted/aggregated view displays as follows
image

If I configure it as you suggest, just through the visual editor, it displays like this - with the ‘1’ from the boolean field. I’m trying not to have 1 appear (effectively have ‘(primary)’ appear instead of ‘1’.
image

The underlying xml when just using the visual editor is now this:

<format name="Collector" title="Collector" class="edu.ku.brc.specify.datamodel.Collector" default="true">
		<switch single="false" field="isPrimary">
			<fields value="true">
				<field sep=" ">agent.lastName</field>
				<field sep=" , ">agent.firstName</field>
				<field sep="  (primary)">isPrimary</field>
			</fields>
			<fields value="false">
				<field>agent.lastName</field>
				<field sep=" , ">agent.firstName</field>
			</fields>
		</switch>

You can see it differs to my customisation, by not specifying a format for the boolean field.

Thanks,
Zoe

Thank you for the screenshots @zoe.warner! At this time, modifying the format attribute for fields in a table format isn’t possible. Formatting a boolean field with a field format is also currently unsupported. However, manually setting the format for a field in a table format with the xml editor will not be overwritten. Even if changes are made to the format using the visual editor, the boolean field format will remain unless the conditional format is unchecked in the visual editor.