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
