Evaluation Query – Data Extracts/Exports

Celtic

Hi,

Currently evaluating AWARE as a replacement to MS Access. All is going well (I think!) and have been really impressed so far with both the product and the forum which has answered a lot of my questions.

One of the key tasks my apps need to be able to do is to Run/Export data extracts on a periodic basis (Qtr, Month etc). This would involve taking attributes from several related objects and then exporting this data into csv format. This data is then imported into another system. An extract would be generated manually.

From what I’ve read this should be possible but as I am complete newbie here I would be grateful for any advice on the approach needed.

I’d like to put together a simple proof of concept to demo this.

Some basic info as an example.

Objects
Patient (this has 20 attributes)
Event (this has 50 attributes)
Treatment (this has 15 attributes)

A Patient can have one or more events.
An Event can have one or more Treatments

I would want to create an extract to combine 15 attributes from Patient and 40 attributes from Event and 5 from Treatment. I don’t need to select specific attributes at runtime as the attributes to be extracted are set in stone. So there will be a lot of attributes needing to be extracted.

Hopefully this provides useful background on what I’m looking to achieve.

When combining objects the query functionality does not seem to show the related fields on a single row, so there is something I’m missing??. In MS Access you would just build a View or query from multiple tables. Have also experimented with the Export feature which is good but I need to have the query that combines related objects and attributes.

Any thoughts or even a sample BSV would be much appreciated!

Cheers

Simon.

tford

Hi Simon,

It looks like AwareIM should be well suited to meet this requirement.

The key is to have make shortcuts to the higher level attributes. For example,

1) In Treatment BO, shortcut in Event.Venue or Event.RoomNbr
2) In Treatment BO, shortcut in Event.Patient.Name

You can also shortcut Patient attributes into Event BO in a similar way.

After this info is included in Treatment and Event BO via shortcut, they are available to include in Queries & therefore Exports.

Tom

Celtic

Hi Tom,

thanks for advice on this. This would work form what i've tested thus far.

Seems quite strange concept doing that as your effectively duplicating a large number of attributes within related objects. This might mean you could have objects with a 100+ attributes!

I thought using the Object Groups might be the may but this is for something different.

so if I want to create a query to export key attributes data on all three objects (say 60 attributes) then there needs to be a shortcut on the lowest level object for all those attributes located on the Patient and Event Objects?

many thanks

Simon

tford

so if I want to create a query to export key attributes data on all three objects (say 60 attributes) then there needs to be a shortcut on the lowest level object for all those attributes located on the Patient and Event Objects?

Yes.

One idea for organization purposes when you have a lot of shortcuts: put a sc_in front of the attribute names. This will allow you to sort them all in one section of the BO.

Celtic

Thanks for the tip on the naming convention Tom!

On experimenting with the exports I have few more questions.

I built a query (EXTRACT) based on the lowest level object (TREATMENT) this includes shortcuts to fields in the CLIENT and EVENT objects. I run the query using the Standard menu item called Search Objects, and this produces a list of all records and displays all query columns, I then download this using the export icon into csv. All good.

Q1. Any limit to the number of onscreen attributes? Fine with the need to scroll.

However when I use the Standard menu item Export and then use the query to produce the extract and select “Export All Attributes” (assuming is means all the query attributes) it only outputs the columns in the TREATMENT object only and ignores the SC fields. It also provides the date format used as part of some date attributes headers. I’m expecting it to do the same as when exporting as part of a query.

When running it as part of a process it also has the same outcome as above.

Q2. Any reason why this is?

When I re-run the Export from the menu item and using “Selected Attributes” it exports all columns as expected. Assumedly when exporting its using the actual column names in the database rather than the label headers when exporting as part of a query.?

I think here are some obvious things I’m missing here!

tford

Q1. Any limit to the number of onscreen attributes?

None that I know of.

Re: different export results when using a 1) query in a process 2) Standard | Export 3) Export button on a query. I created a test bsv that demonstrates this & sending it to support this morning.

Celtic

cheers Tom much appreciated, I had a bsv ready to go also just in case :!:
hopefully will hear from them when Oz comes back on line.

aware_support

We have received an e-mail from Tom and will have a look at it shortly.

aware_support

This will be fixed in the next build

Celtic

many thanks Support

Celtic

many thanks Support and to Tom for send it through!

rocketman

Might have got the wrong end of the stick here, but with such a complex build - might it be worth considering creating a non persistent BO, doing straightforward FINDS where x = y for all the bits you need. then CREATE instances in the non persistent BO WITH [nonpersist.attrib1 = patient.arttrib1, nonpersist.attrib2=event.attrib1 etc etc and then exporting that.

If the items needing to be exported are set in stone, it might be a more efficient use of DB resources . saving all those shortcuts

Of course .... I've never tried this but have been thinking it through for a similar export requirement

Celtic

sounds a bit advanced for me right now but yes the attributes to be exported are set in stone, so will look into this when I have got over few other learning gateways!!

cheers

aware_support

Build 1188 addresses the issue of exporting shortcuts by the EXPORT action.

tford

Celtic,

You may also want to look at :

Celtic

cheers Tom!