Kettle OpenVPMS Loader - problem with Account Type lookup

Submitted by Guest on Thu, 20/09/2012 - 18:18

Implementation discussion

I am trying to load customer data (from an RxWorks conversion) and am having touble with the Account Type. The field line reads:

Field name: ACCOUNTTYPE
Map to: <party.customerperson>type[0]
Exclude null: Y
Value: <lookup.customerAccountType>$value
Remove default objects: N

(I took this from the 'VC Customer - Information' sample transform and added an abort step.)

I get the error:
Customer.0 - Failed to process row 327: Failed to retrieve object: <lookup.customerAccountType>Normal
Abort.0 - Seen row nr 19 : [327], [Li], [Kiu Wing], [Miss], [null], [N], [2003/05/11 17:34:45.000], [Y], [Normal]

The output of the SQL query:
select lookup_id, name, code from lookups where arch_short_name = "lookup.customerAccountType";

lookup_id	name	code
257	Bad Debt	BAD_DEBT
468	Cash Only	CASH_ONLY
472	Creature Comforts	CREATURE_COMFORTS
126	Monthly	MONTHLY
157	Normal	NORMAL
7	Pensioner	PENSIONER
36	Personal Friend	PERSONAL_FRIEND
366	Pet Shop Priviledge	PET_SHOP_PRIVILEDGE
69	Staff Member	STAFF_MEMBER
16	Valued Client	VALUED_CLIENT

so 'Normal' is certainly there. I adjusted the script step to capitalise to 'NORMAL' but the same error occurs.

What am I doing wrong?

Re: Kettle OpenVPMS Loader - problem with Account Type lookup

Submitted by tanderson on Thu, 20/09/2012 - 21:23.

If you get the same error after capitalising ACCOUNTTYPE, there's a problem with your input step.

If its capitalising correctly, it will try and retrieve <lookup.customerAccountType>NORMAL

Re: Kettle OpenVPMS Loader - problem with Account Type lookup

Submitted by tgething (not verified) on Fri, 21/09/2012 - 08:41.

Tim (A) - attached is the log and the Kettle transform. As you will see from the log it is trying to retrieve NORMAL and its getting the error message:
Failed to retrieve object: <lookup.customerAccountType>NORMAL

Can you see what is wrong in the transform?
I am wondering if the sample has not been updated to reflect any changed archetype in 1.5.

Regards, Tim G

Attachment	Size
RxClient test2.ktr	12.75 KB

Re: Kettle OpenVPMS Loader - problem with Account Type lookup

Submitted by tanderson on Fri, 21/09/2012 - 12:25.

OK - I think I see the problem.

The <archetype>$value form of the mapping is used when the value refers to an object identifier. As you have the lookup code rather than its identifier, you need to use the <archetype>name=value mapping.

In your case, it will be: <lookup.customerAccountType>code=$value