[Scripting] Get new Field

[jumiprado]

Billing
ClientID
Bill_ID
Period
Amount
Bill_Type
Date_Billing

I give an example for BILLING table:

ClientID	Bill_ID	Period	Amount	Bill_Type	BILL_CANCEL	Date_Billing
Client_001	BILL_0001	may-15	100	Bill	0	01/05/2015
Client_002	BILL_5030	Apr-2015	40	Bill	0	04/04/2015
Client_002	Bill_5043	Apr-2015	-20	Credit Note	BILL_5030	06/04/2015
Client_003	BILL_6074	jun-15	50	Bill	0	07/06/2015
Client_001	BILL_0001	jul-15	-30	Credit Note	BILL_0001	05/07/2015

Collection
ClientID
PaymentID
Period
Amount
Payment_Type
Date_Collection

ClientID	PaymentID	Period	Amount	Payment_Type	Date_Collection
Client_001	P_3059	May-2015	80	Payment	04-05-2015
Client_002	P_5492	Apr-2015	20	Payment	07-04-2015
Client_003	P_8765	Jun-2015	50	Payment	14-06-2015

I Need to create a new table similar to this:

ClientID	Bill_ID	Period	Amount	BILL_CANCEL	PERIOD_CANCEL	Date_Billing
Client_001	BILL_0001	may-15	100	0	0	01/05/2015
Client_002	BILL_5030	Apr-2015	-20	0	0	04/04/2015
Client_002	Bill_5043	Apr-2015	-20	BILL_5030	Apr-2015	06/04/2015
Client_003	BILL_6074	jun-15	50	0	0	07/06/2015
Client_001	BILL_0899	jul-15	-30	BILL_0001	may-15	05/07/2015

Thanks youu!

[Peter_Cammaert]

In your script, create a Bill_Cancel table with some code like this:

BILL_Cancel:

LOAD BILL_ID AS BILL_CANCEL,

     Period AS PERIOD_CANCEL

RESIDENT Billing

WHERE BILL_Type = 'Credit Note';

Reload and add field PERIOD_CANCEL to your main dashboard object. Associative logic will perform its magic.

If you need this PERIOD_CANCEL field as an extra column in the internal Billing table itself, convert BILL_Cancel into a Mapping table, transfer Billing into a second resident table and at the same time use

:

applymap('BILL_Cancel', BILL_CANCEL, 0) AS PERIOD_CANCEL,

:

Best,

Peter