Job Runner
Convert Record Rows to Columns
Overview
Convert records whose values match the specified key into a group to another record.
Usage
This Do behaves in a complex way as the following example:
As shown above, in the unconverted app, a team’s performance is represented by one record per quarter. The converted app summarizes grades by year, with fields separated for each quarter. This example converts the quarterly “rows” to Q1-Q4 “columns”.
First, get the records before converting and set them to the records parameter. For key fields, select Team and Year.
The important thing is conversion mapping, which is configured using the if function as follows:
| Value | Set To Field |
|---|---|
| = Team | Team |
| = Year | Year |
| = if(Quarter=1, points earned) | Q1 |
| = if(Quarter=2, Points earned) | Q2 |
| = if(Quarter=3, Points earned) | Q3 |
| = if(Quarter=4, points earned) | Q4 |
With this mapping, if the Quarter value is 1, the points earned are mapping to the converted Q1 field, if the Quarter value is 2, they are mapping to Q2, and so on. Mapping of teams, years as well as Q1-Q4 is required.
In this example, first, group records with the same combination of team and year at the source and map them to one record at the destination. There are four groups called “Osaka,2021,” “Osaka,2022,” “New York,2021,” and “New York,2022,” so they can be converted to four records.
The records in the group are not mapped to the destination altogether, but are mapping line by line. Therefore, aggregation functions like max and sum are not available. Overwrites the field value if the value to set is not empty during mapping. So in the above example, it should not be like if(quarter=1, points earned, 0). This will overwrite the value zero if the quarter is not 1.
The result of the action is the transformed record. Note that this record has not yet been saved anywhere. It is also not automatically saved. To save the converted record, connect to Add Records from Other Records etc.
Parameter
Set Records, Key Fields, Tatarget App, and Mapping.
Records
Select the Action you used to retreive the target records.
Because of its nature, this Do is meaningless if it does not contain multiple records. Note that after connecting records with When One Record is Ready, the records will become one row and the conversion will have no effect.
Key Field
Select the key fields in the records. Multiple selections are possible.
This parameter accepts the following field types:
- Text
- Number
- Calculated
- Radio button
- Drop-down
- Link
- Date
- Time
- Date and time
- Lookup (string)
- Lookup (number)
This parameter does not accept fields that are inside tables.
Target App
Specify the destination app.
The app does not store records, but is needed to determine the list of fields to convert to.
Mapping
Enter the conversion mapping.
Occurrence of record Retrieval Criteria
| Criteria | Occurrence |
|---|---|
| When Record Is Ready |
When One Record Is Ready occurs once per record converted.
If there were no records, neither would occur.