Is lookup.reminderType missing ??

Submitted by Guest on Mon, 22/10/2012 - 14:46

Implementation discussion

I am trying to load product reminder data using Kettle. I have the products loaded (via Kettle), and I have the Reminder Types loaded (via dataload). I want to use an OpenVPMS Loader transform step as follows:

Line 4 is the problem. Unlike line 3, where I know the id of the product because I loaded it with an earlier Kettle transform, in line 4 where I need to reference the reminderType, I do not have an id (because I used dataload to load the reminderTypes). Since there is no lookup.reminderType archetype defined, the Value entry for line 4 is not correct and cannot work.

Do I have to use Kettle to load the reminderTypes (so that I have an ID for them)?

Or is there some way of getting a reference to the desired reminderType? [I note that all the things set up via Administration|Types appear to have no lookups, so wonder if I am missing something, and there is a way to reference an item defined as a type.]

Regards, Tim

Re: Is lookup.reminderType missing ??

Submitted by tanderson on Mon, 22/10/2012 - 19:13.

The target of the entityRelationship.productReminder is an entity.reminderType, not a lookup.reminderType:

       <node name="target" displayName="Reminder" path="/target"
              type="org.openvpms.component.business.domain.im.common.IMObjectReference" minCardinality="1">
            <assertion name="archetypeRange">
                <propertyList name="archetypes">
                    <propertyMap name="archetype">
                        <property name="shortName" value="entity.reminderType"/>
                    </propertyMap>
                </propertyList>
            </assertion>
        </node>

If you can't refer to the entity.reminderType by Id, but can derive its name, you can do something like:

Field Name	Map To	Value
ReminderTypeName	<entityRelationship.productReminder>target	<entity.reminderType>name=$value

Also, the line:

Field Name	Map To	Value
oID	$oIID<product.service>reminders[0]	<entityRelationship.productReminder>$value

is not needed (and isn't quite right). You don't need to explicitly add the relationship to the product.service; creating the relationship will add it to both the product.service and the entity.reminderType.

Re: Is lookup.reminderType missing ??

Submitted by tgething (not verified) on Tue, 23/10/2012 - 03:25.

Tim A - thanks for this. I am nearly there. I know the exact name of the Reminder Type. The transform now works - BUT only for the reminders with a single word name. ie "Worming" and "Desex" work, but not "Rabies Vaccination" or "Liver Recheck", and for all the 'multi-word' reminders I get errors like the following:

Failed to process row IR63: Invalid reference: <entity.reminderType>name=Feline Leukaemia Vaccination
Failed to process row IR64: Invalid reference: <entity.reminderType>name=zzBlood Donation
Failed to process row IR65: Invalid reference: <entity.reminderType>name=Rabies Vaccination

I looked in the database to see if the name is being held in some scrambled format - but as you can see below, its not. So then I guessed that as part of the lookup you might be adjusting things so I tried both removing the spaces and replacing them by underlines. No good. [I didn't really expect it to work because I suspect you are using the entity_arch_sn_name_idx index to do the lookup - and as per below the names are held as is.]

So, apart for renaming all the reminders to have single word names, how do I get things to work?

Regards, Tim G

Re: Is lookup.reminderType missing ??

Submitted by tanderson on Tue, 23/10/2012 - 11:57.

The name=$value form is somewhat limited by the fact that it doesn't support spaces in the value.

It was initially developed to retrieve lookups by code.

You've got several options:

load the entity.reminderType via the plugin. This gives you access to its Id.
use a Database lookup step to retrieve the existing entity.reminderTypes to get their ids given their names
temporarily massage the entity.reminderType names so they have no spaces in them

-Tim

Re: Is lookup.reminderType missing ??

Submitted by tgething (not verified) on Wed, 24/10/2012 - 15:59.

Tim A - thanks for this. I chose 2 - 'use a Database lookup step'

The lookup works and returns the entity_id. However the loader is not happy and returns errors like the following:

2012/10/24 15:59:09 - itrem-ser.0 - Failed to process row IR84: Invalid reference: 229
2012/10/24 15:59:09 - itrem-ser.0 - Failed to process row IR85: Invalid reference: 210
2012/10/24 15:59:09 - itrem-ser.0 - Failed to process row IR86: Invalid reference: 210
2012/10/24 15:59:09 - itrem-ser.0 - Failed to process row IR87: Invalid reference: 210
2012/10/24 15:59:09 - itrem-ser.0 - Failed to process row IR88: Invalid reference: 228
2012/10/24 15:59:09 - itrem-ser.0 - Failed to process row IR89: Invalid reference: 233

The numbers (229,210,228 etc) are correct - I can see these in the database. Also If I look in the entity_relationships table I can see the entries created by using the name lookup (which worked for Worming & Desexing), and these have matching numbers in the target id column (197 for Worming and 204 for Desexing).

I tried the follow guesses of the entry for the Value column:

leave blank [gives Invalid reference error]
set to $value [gives the same error]
<entity.reminderType>id=$value [gives error: java.lang.String cannot be cast to java.lang.Long]
<entity.reminderType>id [gives Failed to retrieve object: <entity.reminderType>id ]

So what's the magic recipe?

The actual transform is attached if required.

Regards, Tim

Attachment	Size
RxItemReminders.ktr	31.78 KB

Re: Is lookup.reminderType missing ??

Submitted by tanderson on Fri, 26/10/2012 - 09:37.

The <entity.reminderType>$value form performs a lookup on the etl_log table to get the corresponding OpenVPMS object with archetype="entity.reminderType" and row_id=$value.

For this to work, the plugin must be used to load the corresponding "entity.reminderType".

You haven't loaded the entity.reminderTypes using the plugin so there will be no matching rows in etl_log.

Ideally, you'd use the <entity.reminderType>id=$value form. This should retrieve the OpenVPMS object given its archetype "entity.reminderType" and node "id" = $value. Unfortunately, the id node is a long, and the value is a string, hence the error: java.lang.String cannot be cast to java.lang.Long.

I guess there is a few options:

1. query on the linkId column instead of the id column. This is a string rather than a long. For this to work, you'll need to add the following to entity.reminderType:

Your database lookup would then return the entities.linkId column rather than entities.entity_id.

2. Load the entity.reminderTypes using the plugin. You can then use the <entity.reminderType>$value form.

3. Massage the entity.reminderType names to remove spaces, so you can use <entity.reminderType>name=$value

Re: Is lookup.reminderType missing ??

Submitted by tgething (not verified) on Sat, 27/10/2012 - 00:53.

Tim A - thanks for the reply. I used option 2 - switch to using the loader plugin. Drat - I was hoping I wouldn't have to do that since I have the same problem of linking to users so that I can set the author and clinician fields. I suspect that I am going to end up using dataload only for the items for which a lookup is available.

We really need to enhance the dataload program to be able to to add items to the etl_log (ie if started with the -e option, then for anything with an id= parameter, make the appropriate etl_log entry). This would then mean that you could use the convenience of dataload, but still be able to access the links to the dataload'ed items when using the kettle loader plugin. It would also be worth enhancing the dataload syntax so that it could access links stored in the etl_log - this would remove the current limitation that any link references (ie where the format "id:xxxxx" is used) must be to id's defined in the same dataload run.

These enhancements would certainly make the job of the implementor somewhat easier.

Regards, Tim G

Re: Is lookup.reminderType missing ??

Submitted by tgething (not verified) on Mon, 29/10/2012 - 07:58.

The light dawned - there is yet another way. It is possible to load the appropriate information into the etl_log file so that one can reference the items previously loaded by dataload as though they had been loaded by the kettle plugin.

In my case I handled the reminderTypes lookup by rejigging things to load the reminder data using the kettle loader. However, I could not face the work involved in repeating this process for the users, the tills, and practiceLocations (clinics in the RxWorks terminology) and so came up with the idea of injecting the appropriate data into etl_log.

The attached three transforms do the job. Note that these are RxWorks-centric - ie the lookup ID's used are those I need to match the data extracted from the RxWorks system (clinic number for the till and practiceLocation, and userId for the clinician/author).

Regards, Tim