Home » blog » vargam's blog

Implementation of an IQA query framework

Posted March 18th, 2009 by vargam

I am trying to find examples of customers where IQA has been used to create some kind of hierarchical framework.

I am looking for examples of a core library of queries and business objects, such as active members, regional breakdown structures, paid through status, specific event attendance, etc, that are then being used to construct second and subsequent level queries, such as active members who attended a specific event by joining 2 or more existing base queries.

Is this even something that customers / consultants are thinking about when implementing imis? Is it practical / technically possible?

vargam's blog | login or register to post comments | printer friendly version

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Room for improvement

On March 18th, 2009 Stu says:

It can be done, but it can get messy because:

1) When you join the two queries together, you are always working at the lowest level. ie : When you join "All paid thru members" to "All who attended Event A" you will be joining on
NetContact.ID = NetContact1.ID, instead of "All paid thru members".ID = "All who attended Event A".ID So the more subcomponents you ue, the hard it is to decipher.

2) Similar to the above, if you create a calculated field in one of the above queries, it won't be available to join on or display.

3) You can't outer join effectively if it requires a filter to be added. So if you wanted to outer join to show all Formal Salutations if they existed, currently IQA requires you to join on ID and then filter to say the SalutationType is Formal. The act of filtering then enforces an inner join, whereas this is possible when writing SQL direct to the database. So in dealing with a library of queries this would be even tougher.

Perhaps this has changed in 15-1?

login or register to post comments

You can do outer joins with

On March 19th, 2009 Eric Means says:

You can do outer joins with filters, although only relatively simple ones and it's a little complex to set up.

For example, to do an outer join between CsContact and User (show all contacts and their login info, if any):
1. Add CsContact and User as sources.
2. Add a Left Join between CsContact.iMIS Id and User.Contact Master.
3. On the Filters tab, add the desired filter (say, User.User ID).
4. Click the Add Filter button.
5. Select "Or" in the second filter's dropdown, then click Refresh.
6. Add a filter in the second filter area on User.User ID with a Comparison type of "Empty".

This will generate the proper SQL for filtering an outer join:

SELECT vBoCsContact.Company, vBoCsContact.Country, vBoCsContact.MemberType, vBoCsContact.StateProvince, vBoCsContact.City, vBoCsContact.ID, vBoCsContact.FullName FROM vBoCsContact LEFT JOIN vBoUser ON vBoCsContact.ID = vBoUser.ContactMaster WHERE (vBoUser.UserId IS NULL OR vBoUser.UserId = '[value]')

(This should work on any version of IQA as far as I know).

login or register to post comments

Further understanding?

On March 22nd, 2009 vargam says:

From what I read in your comments, it would seem that there are a few options and paths to take:

1) Raise an SMR to ask for some improved naming conventions, as it seems sensible that when selecting properties after choosing the source as a query (whether for joins or for filters or whatever), along the lines of what happens with views - i.e. query name.property name, with only the displayed properties from the original query available, as an example

2) I deliberately thought that the solution would need to involve BOD, so in your second point, when wanting to do more complex things like joining on calculated fields, then this may indicate the need to create a new BO rather than a query

3) As above, it may be about some guidelines of when to use a BO rather than a query

What I am trying to get toward is some kind of recommendation to give to customers that outlines how to use IQA and BOD to create a customised framework for accessing their data. It is really recognising that it is not6 possible to give lay users the raw iqa tool and expect great results, but that to give them a framework that has been specialised for them would be of great value

login or register to post comments

Mick, I don't know if you've

On March 23rd, 2009 Eric Means says:

Mick, I don't know if you've seen the iNNOVATIONS schedule, but there's a session at 3:15 on 4/28 that sounds very much like what you're looking for/trying to develop; the title is "When to Use: iBO, BOD, Web Services, etc." It may not address everything you're asking for, but it sounds like it would be a good start for developing that kind of guidance.

login or register to post comments