Data Import & Export: Dependent Records

Dependent Records

Use a UUID placeholder to import dependent records without needing to populate the parent key field into the import file for the child records.

This feature is available in the following places:

1. Setup / Import Table Data
2. APIs → Import / Export → Request a table data import)
3. APIs → Import / Export → Insert or update a record with auditing

Below is a summary of the steps required without this function, using approvals as the example. The same principle can be used for any table relationship. eg. and .

1. Import records.

2. The fields are populated during the import.

3. Export the detail of the record you have just imported, , or identify the some other way.

4. Update the records you want to import to contain the right value to link the dependent records to their parents.

5. Import the updated records.

Below is a summary of the same example as above, but using this function.

1. Populate each field with a unique UUID value. Populate each field with the same UUID value as it's parent.
2. Import the records. During the import, the fields are populated and the and the original UUID value are stored in .
3. Import the records. During the import, the fields are populated with their parents value. The details stored in enables this.

Detailed Example of using a placeholder value to import dependent records without reference

You want to create a batch of three approval records. Each of them has at least one corresponding maintenance row.

To do this, you need a universally unique identifier (UUID) for each approval record you are creating.

A UUID can be generated in a number of ways. One way is by generating them using a generator tool such as: https://www.uuidgenerator.net/ (use Version 4 for best results).

The format of the UUID must be: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. The UUID is not case sensitive.

Once you've generated the required UUID values, put the UUID into the place of the table's Primary Key (the Unique ID of the table).

The syntax in the field is 'ref(UUID)'. E.g. ref(xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx)

Here's a simple example:

Import file fm_approvals.csv

When the approvals were imported, each UUID was stored in along with a field value and table name.

Import file fm_maintenance.csv

Any time the UUID ref(95328c94-d4c1-468b-b30f-b64116ab91f5) is used, the system will look it up in and populate the stored fieldvalue (in this case the approvalid of 111111).

Importdata 1link Approvals

The 1Link Approvals data import option allows the user to import approval records generated by 1Link into the catch-e database if so configured. These records can then be viewed as approvals and maintenance records. If the record is correct, the Approval status is set to “Posted” and the GL table is updated so that supplier invoices are ready for payment and recharges to clients are ready for billing. The ‘External Order’ field that displays on the Approvals screen is the 1Link reference number.

This process can be performed manually, but is best set-up to run nightly as part of an automatic web service.

Validation Rules Summary

• Match the 1Link supplier and contract to Catch-e records
• Create the appropriate non-recharge, recharge and partial recharge lines required
• Adjust the 1Link GST data to suit Catch-e’s data structure
• Validate the Odometer readings and untick ‘Valid Odometer Flag’ if reading is invalid
• If the validation process fails for an approval, the approval is not loaded and is set aside for review.

Transaction Outcomes

There are three outcomes that can result for an uploaded 1Link approval

1. Posted – the approval record is in Approvals/Maintenance and has been posted to the General Ledger file.
2. Active – the approval record is in Approvals/Maintenance, but could not be posted (typically because of missing information or inconsistent record profiling between 1Link and Catch-e)
3. Invalid – the approval record is not copied into Catch-e

Import Process

The import process uses the following steps.

1. Select Table: Drop down list of available tables
2. Upload File: Select the file using the browse feature
3. Validate File: The system will check the number of fields in the file and accept or reject
4. Import File: The system will import the file
5. Repeat: Another file may be selected for importing.

Importdata 1link Approvals Setup

SFTP Connection

Scheduler Jobs

Set up three scheduler jobs using the methods listed below.

1Link Setup

17.1 Create fmsuppliers and fmsupplierfranchisevehicles mapping from scheme operators list supplied by 1link.
17.1.1 Create fmsuppliers record for each Account Name from scheme operators list. Map 1link Account Number to fmsuppliers.externalcode. 17.1.2 Create supplier code from Account Name. Add 1,2,3 etc at the end to make them unique if required. 17.1.3 Create one fmsupplierfranchisevehicles record for each scheme operator. Map 1link Account Number to fmsupplierfranchisevehicles.3rdpartyid and 1link Site ID to fmsupplierfranchisevehicles.3rdpartysite_id.

17.2 Setup NVIC (Glasses) or Variant (Redbook) Code to 1Link URN mapping in fmvariantcode_mappings.

17.3 Setup fminterfacevmrsmappings (fminterfacevmrsmappings.productcode => X6.1linkjobcode + '-' + X6.reasoncode). Create mappings for Tyre Codes and 5 reason codes below as not all reason codes are relevant for Tyres.
X6 is Item Line Details row in the 1Link Invoice file.

A02 Below Legal Limit
A04 Client Request
A07 Damage
A18 Routine
A21 Worn

17.4 gbqueries and gbcontrols setup.
17.4.1 Create following query in gb_queries to generate Summary Report for 1Link Invoice Import Process.

SELECT t1.import_id,t3.created,t1.external_order_number,t1.approval_id,t4.contract_id,t7.posting_map_code AS type, t6.supplier_code,t6.name,t1.invoice_date,t1.invoice_no,SUM(IF(t2.recharge_flag = 'Y', total_cost,0)) AS recharged_cost,SUM(t2.total_cost) AS total_cost,t1.status_flag,t1.onelink_approval_id AS 'import Approval Id'FROM fm_onelink_approvals AS t1INNER JOIN fm_onelink_maintenance AS t2 ON t2.onelink_approval_id = t1.onelink_approval_idINNER JOIN gb_imports AS t3 ON t1.import_id = t3.import_idLEFT JOIN fm_contracts AS t4 ON t4.contract_id = t1.contract_idLEFT JOIN fm_suppliers AS t6 ON t6.supplier_id = t1.supplier_idLEFT JOIN gl_posting_maps AS t7 ON t4.posting_map_id = t7.posting_map_idWHERE t3.interface = 'onelink'AND t1.import_id = (SELECT MAX(import_id) FROM gb_imports)GROUP BY t1.onelink_approval_idORDER BY t1.status_flag, t1.approval_id;

17.4.2 Create following query in gb_queries to generate Validation Report for 1Link Invoice Import Process.

/* Generate an exception report with following details * Invalid records with reasons. * Exception records with reasons. */SELECT t1.import_id,t1.onelink_approval_id AS 'Onelink Approval Id', t2.onelink_maintenance_id AS 'Onelink Maintenance Id', t1.contract_id AS 'Onelink Contract Id',IFNULL(t4.contract_id, 'Invalid Contract Id') AS 'Catch-E Contract Id', t1.approval_id, t1.external_order_number,t1.supplier_id AS 'Onelink Supplier Id',IFNULL(t6.supplier_id, 'Invalid Supplier Id') AS 'Catch-E Supplier Id', t1.invoice_no AS 'Invoice No',IFNULL(t2.product_code, 'Missing Onelink Job/Reason Code') AS 'Product Code', t1.status_flag AS 'Status', IFNULL(t5.exception_flag, 'yes') AS 'VMRS Mapping Exception',IF(t2.recharge_flag = 'N' && t2.onelink_recharge_flag = 'Y', 'Yes', 'No') AS 'Recharge Exception',IF(t2.posting_class_id = 0, 'No matching Posting Class', t2.posting_class_id) AS 'Posting Class Id', IF(t2.vmrs_code_id = 0, 'No matching VMRS Code',t2.vmrs_code_id) AS 'VMRS Code Id', t2.description AS DescriptionFROM fm_onelink_approvals AS t1, fm_onelink_maintenance AS t2, fm_interfaces AS t3LEFT JOIN fm_contracts AS t4 ON t4.contract_id = t1.contract_idLEFT JOIN fm_interface_vmrs_mappings AS t5 ON t5.product_code =t2.product_code AND t5.interface_id = t3.interface_idLEFT JOIN fm_suppliers AS t6 ON t6.supplier_id = t1.supplier_idWHERE t2.onelink_approval_id = t1.onelink_approval_id and t2.line_type != 'part' AND t3.interface_code = '1LINK' AND t1.status_flag IN('invalid', 'exception')AND t1.import_id = (SELECT MAX(import_id) FROM gb_imports)GROUP BY t2.import_id, t2.row_no_x6ORDER BY t2.import_id, t2.row_no_x6

17.4.3 See Global Control onelinksftpconnection) details.

17.4.4 onelinksummaryreport - see example below.
17.4.5 onelinkvalidationreport - see example below.

$onelink_summary_report['query_id'] = '100016';$onelink_summary_report['recipients'][0]['name'] = 'John Smith';$onelink_summary_report['recipients'][0]['address'] = 'JohnSmith@abc.com.au';$onelink_summary_report['recipients'][1]['name'] = 'Denis Pearl';$onelink_summary_report['recipients'][1]['address'] = 'denisp@abc.com.au';$onelink_summary_report = serialize($onelink_summary_report);$onelink_validation_report['query_id'] = '100017';$onelink_summary_report['recipients'][0]['name'] = 'John Smith';$onelink_summary_report['recipients'][0]['address'] = 'JohnSmith@abc.com.au';$onelink_summary_report['recipients'][1]['name'] = 'Denis Pearl';$onelink_summary_report['recipients'][1]['address'] = 'denisp@abc.com.au';$onelink_validation_report = serialize($onelink_validation_report);REPLACE INTO gb_controls VALUES('fm', 'onelink_summary_report', '0000-00-00', '$onelink_summary_report'),('fm', 'onelink_validation_report', '0000-00-00', '$onelink_validation_report');

17.5 Schedule following Jobs in the scheduler.
17.5.1 Catch-E to 1Link Export.

17.5.1.1 fm->onelink->exportVehicle : This exports all contracts (fve.txt -> vehicles) and Clients (fce.txt -> Customers) to 1link.

17.5.1.2 fm->onelink->exportMaintHistory : This exports all maintenance history (vmYMD.txt) to 1link.

17.5.2 1Link Invoice Import into Catch-E. Schedule this job very early in the morning say around 6:00am. The invoice file to be imported is only avaiable on the FTP Server very late at night maybe around 5:00am or so AEST time.

17.5.2.1 fm->onelink->inportInvoices. Invoices can also be imported manually using menu option Import Data / 1Link Approvals. This menu option is excluded by default and needs to be included in Menu by modifying client specific hiermenu.

Importdata Insurance Data

Insurance data may be sent by an Insurance company that contains details of policy premiums, policy numbers, cover dates etc.

Using this menu, you can import this data into a table using a standard import format. Once imported Scheduler job/s may be set up to use the stored data to update Contract fields.

Client Specific development is required to turn on the import functionality. Please contact your Account Manager for further details.