Variable TAKE BEST

[robleer]

Hi,

I need to run a query which have to show a few records. Sometimes 5, sometimes 7 a.s.o.. I never know how many it has to show, so this is variable.

I use the 'TAKE BEST' way and try to set a value to make the result variable.

Something like:

FIND ALL AnObject TAKE BEST SystemSettings.Counter

This does not work of course, but is there a cleverway to do this?

TIA, Rob

[kklosson]

Maybe try the LIMIT option?

FIND Account WHERE Account.State = 'OPEN' AND Account.Balance > 1000 LIMIT 20,3

[robleer]

Nope, that does not work. Any other ideas how to achieve this?

[kklosson]

Are the number to be returned limited enough that you can IF your way through it?

IF SomeChoice = 1 THEN
Query Take Best 1
ELSIF SomeChoice=2 THEN
Query Take Best 2
etc...

[robleer]

No, as I wrote, I never know in advance how many records I need.

So therefore it must be variable. Tnx Rob

[kklosson]

I'm not sure why the LIMIT switch is a non-starter since the values can be expressions whereas TAKE BEST required a literal integer. From the documentation example:

If this construct is used after the FIND action, only the specified rows will be returned. The first parameter indicates the first row number to be returned (starting from 1) and the second parameter indicates the number of rows to be returned. For example:

FIND Account WHERE Account.State = 'OPEN' AND Account.Balance > 1000 LIMIT 20,3

So, your query might be:
FIND [myQueryString] LIMIT 1, [variable]

This should return the first X records of the query results, but I've never tried to implement the LIMIT switch.

[BobK]

Take a look at the EXEC_STRING Action.

You can do something like
EXEC_STRING 'FIND ALL AnObject TAKE BEST ' + SystemSettings.Counter

[robleer]

Hi Bob,

That does not work also, because I cannot do anything with the records found.

This is my example process:

FIND ALL AnObject //this BO contains 6 records

AnObject.TryNr=0 //set everything to zero

EXEC_STRING 'FIND ALL AnObject TAKE BEST ' + SystemSettings.Counter // this selects 3 records

AnObject.TryNr=1 //Sets the attrib ´TryNr´ of 3 records to 1

DISPLAY QryAnObjects // THIS SHOWS all 6 records and all 6 have TryNr=1 ??? Should be 3 with 0 and 3 with 1

Even if you replace 'EXEC_STRING 'FIND ALL AnObject TAKE BEST ' + SystemSettings.Counter' by a normal statement ´FIND ALL AnObject TAKE BEST 3´ it does not work. Looks like a TAKE BEST does not put 3 records in context, but puts all records in context??

Tia,

Rob

[BLOMASKY]

Can you get a sequence in the SQL table? (Can easily be done by a stored procedure). Then, you can have a filter like:
FIND BO WHERE BO.seq <= SessionVars.noRecs


Bruce

[PointsWell]

Looks like a TAKE BEST does not put 3 records in context, but puts all records in context??

TAKE BEST isn’t working because your first process

This is my example process:

FIND ALL AnObject //this BO contains 6 records

AnObject.TryNr=0 //set everything to zero

Has brought every AnObject into context

To fix this you’d need to run

EXEC_STRING 'FIND ALL AnObject TAKE BEST ' + SystemSettings.Counter // this selects 3 records

AnObject.TryNr=1 //Sets the attrib ´TryNr´ of 3 records to 1

DISPLAY QryAnObjects // THIS SHOWS all 6 records and all 6 have TryNr=1 ??? Should be 3 with 0 and 3 with 1

As a sub process with zero input BOs

If you run a more refined search after a more general search in the same process, then the more general search will be the set of records that are in context.