Named Find - One 2 Many relationship problem??

[galewis]

Hi .... I hope someone can help a novice with a named find that's not working properly using Approach 9 (millenium edition).

I have used the 'assistant' to help me generate this find:-
___________________________
In database "ORDERS2", find all records in field "CLIENT" that are exactly equal to "Park Air Electronics Ltd" or "Park Air Systems Ltd"
AND
in database "SHIPMENT2", find all records in field "BATCH_STAT" that are exactly equal to "WIP/SHORT", "SHORTAGE" or "FI short"
AND
in database "CONCES2", find all records in field "OS_SHORTAGE" that are not equal to "0"
____________________________

As far as I can tell the database CONCES2 has a one to many relationship with ORDERS2 where CONCES2 is the 'many'. Basically, the OS_SHORTAGE field is listed as a subform with ORDERS2. Problem is that unless the value of OS_SHORTAGE is not equal to "0" in the first line of the subform, the find won't pick it up.

I used to be a basic user of DataEase for DOS a long time ago, and I'd have got around this problem by using the 'ANY' command i.e. if any of the OS_SHORTAGE is not equal to "0", then list them. However, this find seems to be only checking the first line in the subform list.

I hope someone understands this even though most of my terminology is wrong.... Here's hoping...

Gary Lewis

 

[paulbent]

This isn't possible within the logic of a find. It also depends on the main table of the view on which the find is run.

You can create a conditional summary calc which will return a non-zero value if any the records in the "many" table meet the criteria. For example

SSum(CONCES2.OS_SHORTAGE) with summary option set to All Records in CONCES2.

If you put this field on a form based on ORDERS2 and in Find Mode (not the Find Asst) enter <>0 as the condition it will find matching order records.

You can also have a conditional summary calc:

SSum(If(SHIPMENT2.BATCH_STAT = 'WIP/SHORT' or SHIPMENT2.BATCH_STAT = 'SHORTAGE' or SHIPMENT2.BATCH_STAT = 'FI short', 1, 0) with summary option &quot;All records in SHIPMENT2)

But these will only work for a find on ORDERS2. If the find is on CONCES2 then you can use fields from ORDERS2 but not from SHIPMENT2 as there is no direct relationship with this table.

Finds using calc fields have to be created on the view in Find mode. Once the find has run, it can be saved as a named find by overtyping <Current Find/Sort> in the dropdown list in the Action Bar

Paul Bent
Northwind IT Systems

http://www.northwindit.co.uk