Souce of error when selecting customers and patients after linking with loader.
G'day all,
Finally got all customers and patients loading through a kettle job with 3 transforms, 1 each for customers, patients and the links between them. Before the linking I can search and retrieve both customers and patients, but after the linking, searching on either (customer/patient->search->find) gives the error:
Failed to execute query: {0}.
The job only threw 1 error which I am still working out:
MariaDB [vmd_kettle_op]> select count(*) cnt, error_messages from openvpms.etl_log group by error_messages;
+-------+---------------------------------------------------------------------------------+
| cnt | error_messages |
+-------+---------------------------------------------------------------------------------+
| 27526 | NULL |
| 1 | Invalid reference: <party.patientpet>PET1298. Reference resolves to > 1 objects |
+-------+---------------------------------------------------------------------------------+
2 rows in set (0.78 sec)
Anyone got any ideas?
Regards, Simon
Re: Souce of error when selecting customers and patients ...
Humm - Simon - does the "MariaDB" mean you are running that rather than MySQL. I know that its meant to be a drop in replacement, but I am not sure that anyone has played with it.
However, the error you are getting from the OpenVPMS loader, is not due to the fact that a patient cannot have multiple owners (it can) but is I think due to the way you are linking the customers and patients.
Each record written to the entityRelationship.patientOwner "file" needs to have the customer and patient identified. In this case you would seem to have two records in the etl log with archetype party.patientPet with the id "PET1298".
Try running the query:
select * from etl_log where archetype = 'party.patientPet' and row_id = 'PET12989';
I suspect that you will find two of them. There should be only one.
Regards, Tim G
Re: Souce of error when selecting customers and patients ...
Found that problem! That pet id only appears once in the pet table, which I had checked last night, but the etl_log does have it twice as you suggested Tim. The view for loading into kettle has it twice, once for each microchip. I'lll check and fix that.
What thoughts on the error when searching in OpenVPMS? "Failed to execute query: {0}."
Regards, Simon.
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
"Failed to execute query: {0}." only happened after the linking step. I think I have the mapping correct for the cust to pet link ...
The IdField named CUSTPET is unique for each row in the input.
Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
There should be more information about the error in openvpms-full.log.
In 1.7, this will typically be located in the directory you started tomcat.
Re: Souce of error when selecting customers and patients ...
Humm - you didn't answer the question about MariaDB.
The following shows the sql to query patients and customers - it would be interesting to see what you get compared to what you expect.
[The customer names look funny because this is from my anonymised database, the patient names illustrate the penchant that the Chinese have for naming their pets with two repeated syllables.]
Regards, Tim G
Re: Souce of error when selecting customers and patients ...
Tim, on this test system it is MariaDB, but the production system will be mysql on CentOS6.
That query produces results that look good, from memory of known people and animals, for the first thousand at least.
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
In that case I would do as Tim A suggested and go and look at the openvpms-full.log - normally in the <TOMCAT_HOME> directory. Regards, Tim G
Re: Souce of error when selecting customers and patients ...
Attached is the log produced immediately the Find is clicked for a customer search. Whatever is meaningful is lost in the bulk for me. Perhaps someone who knows what to look for can see the pertinant messages.
Hang on, near the top of the log, can OpenVPMS handle the zero date time that MySQL allows?
Regards , Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
Simon - given that it says at the top of the log "Cannot convert value '0000-00-00 00:00:00' from column 10 to TIMESTAMP", I suspect that the problem is that where the ownership is current (and you do not have an ownership end date) then the activity_end_time should be null and not '0000-00-00 00:00:00'.
Here is a output from my system. As you can see most of the end times are null, which a few non null.
I think that your Kettle transform (if unchanged from the screen shot above) is not setting either the active_start_time or the active_end_time. Hence a) the end time should be null; and b) the start time should be set to the current time (as it defaults to java.util.Date.new() )
It would be interesting to see what your output to the above sql is.
Regards, Tim G
Re: Souce of error when selecting customers and patients ...
Tim, I'll check that sql in a minute, but I have done nothing to load any customer nor patient active dates, only patient birthdates. I was going to extract the Account Creation Date from the accounting data to use for a customer start date, but haven't done this yet.
On a day-to-day basis, how do people generally use these start and end dates? Like when a patient dies etc?
Regards, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
The start date is exactly that - the day the owner acquired the animal - which isnt the same as the birthday of course
The end date is the day the owner sold or relinquished the animal. In this case you would create a new customer to be the new owner and they would have a new relationship with the pet.
Hence the end date would only be applied if the patient changed owners...if it dies the pet is simply marked as deceased but remains linked to the customer...enddate should be NULL.
Interestingly in 1.8 we will add patient locations to the mix. Meaning 1 person can own a pet while its located on someone elses property.
Re: Souce of error when selecting customers and patients ...
Ben - your note about patient locations in 1.8 - that is already in 1.7 - see http://www.openvpms.org/documentation/csh/1.7/customer/edit#patientLocation
Regards, Tim G
Re: Souce of error when selecting customers and patients ...
Well....clearly I am at the bleeding edge :P
I never tried adding them in 1.7 (mind you we run small animals so locations is a bit irrelevant)
Re: Souce of error when selecting customers and patients ...
Thanks all, the loading and linking of customers and patients is working fine now.
One other, possibly related thing is that when selecting a pet, the breed appears in the select list and at the top of the patient information (Description?), but the species and breed fields are empty. Is this because the loader step did not have an entry in the value column pointing to the lookup table?
Regards, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
Simon - can you either email me a screen shot, or upload one here.
Re the loader step - since lookup generation is always enabled (see http://www.openvpms.org/documentation/openvpms-loader-plugin-pentaho-kettle ) I suspect that the species and breeds have been created for you. What do you see in Administration|Lookups|Species and ...|Breeds ?
Regards, Tim G
Re: Souce of error when selecting customers and patients ...
Tim, In the lookups I have the species and breeds loaded from the setup.xml file.
The screenshots show the screen and patient information screens.
Sorry for the delay, we're stocktaking today.
,
Thought I'd put the transform in too.
Regards, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
The patient species and breed codes must correspond with lookup.species and lookup.breed codes.
If you run:
select e.entity_id, e.name, species.value as species, breed.value as breed from entities e left join entity_details species on e.entity_id = species.entity_id and species.name = 'species' left join entity_details breed on e.entity_id = breed.entity_id and breed.name = 'breed' where e.arch_short_name = "party.patientpet"You can see the species and breed codes for each patient.
There must be corresponding entries in the lookups table i.e.:
select l.code from lookups l where l.arch_short_name = "lookup.species"select l.code from lookups l where l.arch_short_name = "lookup.breed"Re: Souce of error when selecting customers and patients ...
Simon - I had a look at my 'Rx Patient' transform (which you have) to refresh my memory of how I did it.
I loaded the breeds and species using data load - and the XML contained stuff like:
As you can see this generated the species name and code (with the code set to the uppercase version of the name). The breeds are loaded by the pairs of lines - the first makes the breed entry with the code being the uppercased version of the name with the first 2 letters of the species added. The second line links the breed to the species.
If Kettle, I generated the breed code by uppercasing the breed and adding the first two letters of the species. [The processing was a bit more complex because I needed to map the breed set in RxWorks to the actual breed - there turned out to be 10 different spellings of 'Shih Tzu'.]
However, I ended up with the codes of both the species and breed, and hence I was able to put these directly into <party.patientPet>species and <party.patientPet>breed - so, example for a Afghan Hound, the species was set to DOG and the breed to AFGHAN_HOUND_DO.
Thus because I knew the codes for each species and breed I didn't have to worry about lookups.
I should say that when I say 'uppercasing' I actually mean upper case, substitute spaces and dashes by underscores and strip apostrohes.
Regards, Tim G
Re: Souce of error when selecting customers and patients ...
Hi Tims,
We have been cleaning up our breeds, where people had entered rubbish, but this load was from the dirty data. However I did expect that the good breed names to be handled fine, but they weren't. The breeds and species were passed to the loader in lower case, but I see in entity_details it has parsed them to upper with underscores etc.. Will get on to converting the breed and species text.
I see the same behaviour with the suburb and state of the customers. The description shows the address, but within the contact record the state, suburb and postcode are empty. I had not loaded the demo postcodesAU.xml with dataload. So to match these they will also need to be upper usw..
The setup.xml file was what I edited to add a dozen extra breeds we needed. So to verify, for the breed, species, state, suburb and postcode to load into the correct "boxes" on the forms, this will happen without lookups in the value column of the loader step, if the loaded text format is an exact match to the codes of that data type being loaded? If a value of <lookup. ??? >$value is passed in the loader step, what text format does that data need to be? Or is it best to leave the value without a lookup and pass the data as an exact match to the existing codes?
Regards, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
Simon my approach was to have the code loaded with out a lookup - for example
I used a table lookup to translate the suburbs to their code, and also to correct the erroneous suburbs - below you can see lines 5,6 & 6 translate the three different spellings of Ap Lei Chau to the same code. Here again, the same trick of having the suburb code include the state. [Hong Kong does not have states but we use HK, KL and NT to indicate Hong Kong Island, Kowloon and the New Territories.]
So what happens if you try to load a suburb code without the suburb being present in Lookups|suburbs?
The loader creates the suburb record - as you can see below I hannened to generate the code CHEUNG_CHAU_HK instead of (because Cheung Chau in the New Territories) CHEUNG_CHAU_NT. (In some cases the RxWorks data had the suburb in the state slot and my code guessed Hong Kong as the state and so generated CHEUNG_CHAU_HK). So the OpenVPMS Loader step created a suburb called CHEUNG_CHAU_HK and set HK as the State - because I had that set as the default state.
And here is the customer record with the bad suburb:
Bottom line - your data should load, but you end up with crappy entries in the lookups.
Regards, Tim G
Re: Souce of error when selecting customers and patients ...
The behaviour I'm getting is slightly different, Tim.
To date, postcodesAU.xml has not been loaded with dataload, the loader step does not have a lookup value, but Exclude Null and Remove Default are both Yes. Single or multiple contact locations load fine, but only with the postcode, not suburb nor state.
With regards to the lookup tables, nothing is added to them either. On one test run a couple of weeks ago, all our bad breeds got added to the lookup table, but I can't remember what was set where on that run - I just changed them fast.
Similar to the breed issue, although our suburbs line up in spelling with the Aust Post codes for locality, but they are propercase with spaces between words, so I assume the loader will want them upper usw..
I'll change the view to convert species, breed, state and suburb to upper and see how that goes.
Regards, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
Simon - the code needs to be uppercase with no spaces, dashes or apostrophes, and unique. Hence Dyer's Crossing 2429 is best converted to DYERS_CROSSING_2429 - if you change spaces to underlines, strip apostrophes, and use underlines as a concatentor to join on the post code - which will make the code unique.
However, stripping spaces and unsing no concatenator would also work - ie DYERSCROSSING2429.
Remember that if you do not pre-load the state and suburb data, then you will end up with a suburb named the same as the code, ie DYERS_CROSSING_2429 with no postcode, rather than Dyers Crossing, postcode 2429.
Regards, Tim G
Re: Souce of error when selecting customers and patients ...
Sorry about the slow reply. I've been working on what you sent. Customers and patients are working fine, as of Sunday. Since then I've been working on products, but have hit the same snag as before, but cannot resolve it.
To get things going as close to financial year as possible, I've scaled down what I originally wanted to bring in, mostly because the data is difficult to get out of the old package. This is fine for now, because some of these areas are where transforms have thrown errors. Probably haven't got the right archetypes sorted out yet.
The current errors are the same as earlier, failed query because of zero date in column 10. I inserted a start date for the unit price, but still have the error. I've attached the transform and sample data if someone might take a look.
The other error in the transform is:
2014/07/02 20:08:26 - Merchandise.0 - Failed to process row A.I.PIPETTES-25S: Failed to retrieve object: <lookup.productGroup>PROCEDURES
I've checed (double and triple) the mapping and value lines against the samples, but still can't work this one out either. The suppliers and product groups and types are loaded through dataload, with base, setup, roles and postcodes.
Any help most gratefully appreciated.
Regards, Simon
PS Since the product_details file was created with supplier and locations, I have dropped these from the transform for now and the supplier details in the xml file are different , so the product_details needs changing to reflect that.
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
1. You need to replace your 0 dates with nulls.
2. Replace <lookup.productGroup>$value with <lookup.productGroup>code=$value
See http://www.openvpms.org/forum/kettle-transform-updating-customers-accoun... for an explanation.
3. The mappings prefixed with $code don't look right e.g.:
$code<product.medication>prices[0]<productPrice.unitPrice>cost
I suspect the $code should be removed
Re: Souce of error when selecting customers and patients ...
Thanks TimA, in reverse order:
The placing of that $code was copying a row from a transform of TimG. Must have misunderstood though, because removing it worked.
Next, adding the code= worked, threw up some other transform errors, but got them sorted.
Lastly, <product.merchandise>prices[0]<productPrice.unitPrice>fromDate is being fed a valid date in correct format (from a transform preview), but entity_relationships.active_start_date is all zeros. Do I have the wrong archetype or entity within productPrice.unitPrice? Also should uom always be passed to productPrice.unitPrice with the prices and date?
Regards, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
Just an update, passing an empty field to :
<product.merchandise>prices[0]<productPrice.unitPrice>fromDate
still gives the same error.
Regards, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
removed comment regarding fromDate (fromDate was the mapping I used)
I have attached 2 ktrs which are part of a job i ran to load my products from my old system to the new.....
Given they both had database links you may not be able to see the mappings but if you can it might help...
I will note that I elected to generate all my startDate's using javascript rather than trying to get java to do a string to date conversion (which in my experience can be flaky)..
Note that in the javascript steps I ensure that the output field is set to be a Date not a string.
I vaguely recall an issue I had when java was setting my dates to 0 when it couldnt convert the strings to a valid date..even though I thought I had them formatted perfectly.
Re: Souce of error when selecting customers and patients ...
One technique I used frequently in kettle was to copy the 2nd to last step (prior to the loaderplugin) to a text file and review them.
I also remember I found it much simplier to load details steps seperately
I would load.
Product Types
Product Classificatioms
Suppliers.
Price Templates
then
Medications - just the name
Medication prices
Link Medication product types
Link Medication classifications
Link Medication suppliers
repeating each for services and merchandise
Each line was seperate transform.
Re: Souce of error when selecting customers and patients ...
Thanks Ben, I'll check the output with the copy-to-file. The initial transform hasd much mor in it. Guess I'll trim it down some more. Will get back with what happens.
Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
I can't help thinking that I'm barking up the wrong tree about the source of the error.
- Added the transform row for <productPrice.unitPrice>fromDate to solve for the error of zero date in active_start_time but error persists.
- <productPrice.unitPrice>fromDate defaults to java.util.Date.new() which is current date time, yes? So fromDate should not be needed anyway?
- the record in entity_relationships with the zero active_start_date has the archetype as entityRelationship.productTypeProduct with name of Product Type
-the <entityRelationship.productTypeProduct>activeStartTime also defaults to java.util.Date.new(), so why the bad dates?
so the fault must be with the <entityRelationship.productTypeProduct>source with value of <entity.productType>name=$value. But I don't know what.
Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
This still has me stumped. I broke up the transform as Ben suggested. The template load worked but the others didn't. I'll attach the transforms and samples to see in anyone has an idea.
Thanks, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
If you don't provide a value for a node in a transform, and the node has a default value defined, then the default value will be used.
I ran your add_products_drugs.ktr transform and it appears to have completed fine, with 2014/07/01 used as the fromDate, and a valid entityRelationship.productTypeProduct relationship.
Note that if you have loaded a row successfully (as far as the OpenVPMS Loader step is concerned), it will create a record in etl_log so that:
If you want to force the Loader to re-load the row, you need to untick Skip Processed. This can lead to unwanted duplicates, which you must then clean up using SQL.
If you want to be certain your transforms are working, start with a clean database and empty etl_log.
Re: Souce of error when selecting customers and patients ...
Thanks Tim. It is strange that the transform worked for you, yet I still get the "Failed to execute query: {0}." error, which points to entityRelationship.productTypeProduct having active start time of zero. I actually drop and recreate the database before each run, to make sure there are no conflicts.
I'll go back to where this same error occurred with the clients and patients and look at that fix. It is bound to be the same base issue.
Regards, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net
Re: Souce of error when selecting customers and patients ...
Make sure you drop etl_log as well.
After you've run a transform, verify the data in the entity_relationships and product_prices tables. E.g.
Re: Souce of error when selecting customers and patients ...
Finally got products working! Only taken me a week.
Templates didn't work because I was referencing a relationship that doesn't exist for them. I just copied the step for services and didn't check the adl file.
Services etc didn't work until I specified the active start date - it didn't use the default.
Just need to add customers and patients back in, adjust stock quantities and we're good to go.
Thanks all, Simon
Simon Slater
Registered Linux User #463789 @ http://linuxcounter.net