Kettle OpenVPMS Loader - how to load customer notes?
I am trying to set up the loading of customer notes, but am having difficulty with constructing the Loader step fields. I think I need something like:
Id Field Name = CUSTID
| # | FldName | MapTo | Value |
| 1 | NOTE | <act.customerNote>note | |
| 2 | CAT | <act.customerNote>category | |
| 3 | CUSTID | <act.customerNote>customer[0]<participation.customer>entity | <party.customerperson>$value |
| 4 | CUSTID | <act.customerNote>customer[0]<participation.customer>act | |
| 5 | AUTHOR | <act.customerNote>author[0]<participation.author>entity | ????? |
| 6 | CUSTID | <act.customerNote>author[0]<participation.author>act |
Here I am using the Customer Id as the ID for the customer Note. This is the ID used to load the <party.customerperson> data earlier.
My problem is the Value setting for field 5. I know the Author name, but there is no lookup to get me a security.user id from the name of a user.
If I had used the Loader to load the Users into the system, then I would know the id. However, I used dataload, and this does not dump the id data into the the ETL table.
I know that the Author setting is non-mandatory (as its Min Cardinality is 0), but I would really like to put an Author name against the note.
I also have a problem with the cusomer link. If I run the transform with the two Author fields remoned (5&6), then I get errors on every row of the form:
Failed to process row C21935: Invalid reference: C21935
where C21935 is the ID Field Name value used to load the customer data earlier (and I can see these in the etl_log table).
I would appreciate any help in showing me the error of my ways.
Regards, Tim G
Re: Kettle OpenVPMS Loader - how to load customer notes?
I don't think the mapping for #4 is correct. You need to be able to link the participation's act node back to the act.customerNote. For that, you'll need a unique identifier for the input row.
Prior to your OpenVPMS Loader step, add an 'Add Sequence' step that creates a 'CUSTOMER_NOTE_ID' sequence. Use this as the "Id Field Name" in the OpenVPMS Loader step:
Id Field Name =CUSTOMER_NOTE_ID
For AUTHOR, I'm assuming you have loaded the user already, and that the AUTHOR field has the legacy system identifier for the author. If this is null and 'Exclude null' is 'Y', then no author will be created for the note.
-Tim
Re: Kettle OpenVPMS Loader - how to load customer notes?
Tim A - thanks for this. It enabled me to make things work. I will add this to my 'OpenVPMS Loader plugin for Pentaho Kettle' article shortly.
Regards, Tim G