Table data transfer between case types

[ISimpson]

As a business we use "Client" and "Debtor" case types by default, although this technique will work between any two different case types. This assumes knowledge of using the "Link" functionality. Let us say there is a table in the Client record that needs to be replicated into the Debtor record, such as on adding a new debt case. In this example imagine a simple table called "tblNewTable" with rows that contain 2 value items in the row: "Item A" and "Item B".

The table structures are created in the database fields in an identical manner for both the Client and the Debtor.

It is not possible to use PUT commands to take the row of data from one table and PUT the data into the row of the other: -

TABLECREATE({Debt link.tblNewTable.Table})
PUT({Item A.Value},{Debt link.Item A.Value})
PUT({Item B.Value},{Debt link.Item B.Value})
TABLESAVE({Debt link.tblNewTable.Table}))

The above will not work, you cannot populate tables directly. Proclaim objects!

1. In the Debtor database temporary store fields to receive the data from Item A and Item B in the Client file.
2. In the Debtor database create a maths routine to take the details from the temporary store fields and save the data to the table in the Debtor record. Say this is called UpdateNewTable

In the Client file a maths or workflow can be written like so: -

acc_ref = {Debt link.case.key}
TABLERESET( {tblNewTable.Table} )
TABLESELECT( {tblNewTable.Table} WHERE {Item A.Value} <> 0.00 )
v_count = TABLESELECTCOUNT( {tblNewTable.Table} )
TABLEFIRST( {tblNewTable.Table} )
WHILE v_count <> 0 DO
PUT( {Item A.Value} , {Debt link.Item A transfer field.Value} )
PUT( {Item B.Value} , {Debt link.Item B transfer field.Value} )
UPDATE( acc_ref , "" )
v-run = {Debt link.UpdateNewTable.Text}
v_count = v_count - 1
TABLENEXT( {tblNewTable.Table} )
END
TABLERESET( {tblNewTable.Table} )


UpdateNewTable exists in the Debtor database and is called from the Client using the "Debt link", effectively running in the debtor environment. UpdateNewTable then contains

TABLERESET( {tblNewTable.Table} )
TABLECREATE( {tblNewTable.Table} )
PUT( {Item A transfer field.Value} , {Item A.Value} )
PUT( {Item B transfer field.Value} , {Item B.Value} )
TABLESAVE( {tblNewTable.Table} )
TABLERESET( {tblNewTable.Table} )
PUT( "", {Item A transfer field.Value} )
PUT( "", {Item B transfer field.Value} )

The effect is to be able to loop through the table in the Client, pass the current row data through to the Debtor and the Debtor taking that row data, updating it's table before returning to the Client record to get the next row.

If anyone struggled with this as much as I did at first then I hope it helps.

[revellbikes]

I've got something similar to this setup to transfer data between a Conveyancing Quote and the main Conveyancing case, all fees and disbursements should transfer during the case creation linked action.

It works perfectly apart from the first line in the table on the newly created case is blank, like it's not saving the data on the first line but all subsequent lines are saved as expected

Code: Select all

# Quoted Fees 
TABLERESET( {Quoted Fees.Table} ) 
vCount = TABLECOUNT( {Quoted Fees.Table} ) 
TABLEFIRST( {Quoted Fees.Table} ) 
WHILE vCount <> 0 DO 
    PUT( {Quoted Fees - Description.Text} , {Conveyancing.Quoted Fees - Holding - Description.Text} ) 
    PUT( {Quoted Fees - Value.Value} , {Conveyancing.Quoted Fees - Holding - Value.Value} ) 
    PUT( {Quoted Fees - Total.Value} , {Conveyancing.Quoted Fees - Holding - Total.Value} ) 
    PUT( {Quoted Fees - VAT Amount.Value} , {Conveyancing.Quoted Fees - Holding - VAT.Value} ) 
    v-run = {Conveyancing.M Quoted Fees Populate.Text} 
    vCount = vCount - 1 
    TABLENEXT( {Quoted Fees.Table} ) 
END 
TABLERESET( {Quoted Fees.Table} ) 

Code: Select all

TABLECREATE( {Quoted Fees.Table} ) 
PUT( {Quoted Fees - Holding - Description.Text} , {Quoted Fees - Description.Text} ) 
PUT( {Quoted Fees - Holding - Total.Value} , {Quoted Fees - Total.Value} ) 
PUT( {Quoted Fees - Holding - Value.Value} , {Quoted Fees - Value.Value} ) 
PUT( {Quoted Fees - Holding - VAT.Value} , {Quoted Fees - VAT.Value} ) 
TABLESAVE( {Quoted Fees.Table} ) 
PUT( "" , {Quoted Fees - Holding - Description.Text} ) 
PUT( "" , {Quoted Fees - Holding - Total.Value} ) 
PUT( "" , {Quoted Fees - Holding - Value.Value} ) 
PUT( "" , {Quoted Fees - Holding - VAT.Value} ) 

I've tried in vain to add random TABLERESET commands etc in to match the code you had setup. I'm also not using TABLESELECT because I want all items in the table to transfer.

Any help much appreciated!

[ISimpson]

I had a number of problems with what I call 'ghost' records when using TABLECOUNT as opposed to TABLESELECTCOUNT. I take this to be because the records are held in the background and not fully got rid of until a case is saved (effectively so you can cancel updating a case and revert to how you were).

What I found worked best was to use TABLESELLECT and use a field that always has data in it, so select where, say, name is not blank and use the selected data (which is effectively everything) and then to a reset afterwards.

[steve]

A new table command TABLECOMMIT has been added to ensure that changes to tables
(particularly deletions) are saved back to the database without the need for the user to exit the
case to force a save of the changes.

341_20191217.3