FR - separate the query UI from the search string

[PointsWell]

If you create a query it contains FIND ... WHERE and you can call that query anywhere you want using DISPLAY QueryName.

If however you have a complex query that you need to build on the fly there is no way to display this.

I have a query which usually has one criteria in it, but sometimes has two or more, e.g.

Code: Select all

FIND Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Jacob'

Sometimes there are jobs that require two mechanics and the first query can't handle that, so it would need to be

Code: Select all

FIND Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Jacob')
AND EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Carl')

Mostly the query will be one mechanic, sometimes it may be two, in outlier cases it might be three.

I can't see a way to build that inside a query and you can't use EXEC_SCRIPT in a query formula to build the FIND statement outside the query place it into a non persisted BO and then EXEC_SCRIPT TempBO.QString.

You can use

Code: Select all

DISPLAY Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Jacob')
AND EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Carl')

But that results in a table of Job instances that is randomised and not meaningful and the operation on records is not controllable (and defaults to EDIT).

What would be useful is to be able to define the layout of a query and then be able to use

Code: Select all

DISPLAY Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Jacob')
AND EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Carl')
USING MyQuery

to override the query's FIND and insert the BOs that you've brought into context with the DISPLAY statement but laid out in the format of MyQuery.

Unless anyone else has encountered a way to make this happen another way.

[customaware]

Are you saying that ....

If I only have 1 Mechanic in Context, then I want this type of query
But
If I have 2 Mechanics in Context, then I want this other type of Query?

Will there be times you have n... Mechanics in Context?

[PointsWell]

Are you saying that ....

If I only have 1 Mechanic in Context, then I want this type of query
But
If I have 2 Mechanics in Context, then I want this other type of Query?

Will there be times you have n... Mechanics in Context?

Answers in reverse order there may be n JobMechanics hence the bringing into context has to be dynamic and can't be placed into a query as a query is static

So when I said

I have a query which usually has one criteria in it, but sometimes has two or more

what I should have typed is I have a need for a query that is sometimes one criteria, sometimes two criteria, infrequently more.

The example above is very simplified the real query has multiple aspects but one part of it that can be one or more elements depending on other criteria.

Its 'easy' to get them into context, but its near impossible to get them into a grid to show in the UI.

[customaware]

Not totally sure if this will work Sean as I have not tested it.... but here is an approach that might work.

You want a dynamic number of Mechanics Names in the Query....

So, I would try and build a String .... say, SystemSettings.MechanicSearchString and assign the names you want....

ie. 'Jacob,Carl,Bob,Roger'

Then change this Query....
FIND Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Jacob')
AND EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Carl')

To This....

FIND Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name IN SystemSettings.MechanicSearchString)

It might work.

[hpl123]

Another solution is populating som temp parent BO with all you need and then run the query on that BO children. I do this here and there for complex scenarios or when Aware doesn't do what I want.

[PointsWell]

Not totally sure if this will work Sean as I have not tested it.... but here is an approach that might work.

You want a dynamic number of Mechanics Names in the Query....

So, I would try and build a String .... say, SystemSettings.MechanicSearchString and assign the names you want....

ie. 'Jacob,Carl,Bob,Roger'

Then change this Query....
FIND Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Jacob')
AND EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name = 'Carl')

To This....

FIND Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND JobMechanic.Name IN SystemSettings.MechanicSearchString)

It might work.

Unfortunately IN evaluates as an OR as in

Code: Select all

FIND Job WHERE EXISTS JobMechanic WHERE (JobMechanic.obJob=Job AND (
JobMechanic.Name = 'Jacob' OR 
JobMechanic.Name = 'Carl' OR 
JobMechanic.Name = 'Bob' OR 
JobMechanic.Name = 'Roger')

Which brings back all of the JobMechanic records and consequently all of the Jobs (if those were the only four mechanics)

[customaware]

I will try and knock up a demo over the weekend.

[PointsWell]

This FR is more adequately described here