This is a seemingly simple question, but I'm stymied. For a data table named PERSONS, I have a edittable, in which each row lists a DATE (column 1) and the correspoding ITEM that the person possessed on that date (column 2). How do I search for persons who possessed a particular item on a particular date? And,how do I search for persons who possessed a particlar item on or before a particular date?

Thanks,

Mark

glicks@compuserve.com

BG-Map Botanical Garden Mapping System [img]http://www.bg-map.com/bgmap.gif[/img]

Mark,

How do I search for persons who possessed a particular item on a particular date?

A classic problem when searching on AMVs.

In a similar situation I made a third MV field - a symbolic field with values concatenated from the first two fields' values - and searched on that. Sometimes one must put some delimiter character between the two fields' values.

And,how do I search for persons who possessed a particlar item on or before a particular date?

I think the method above could work.

Have the item value first in the symbolic, the date value second. And some delimiter character inbetween. I suspect a low value for the delimiter character (e.g, SPACE or a control character, and not the Revelation delimiters) will work best. The date value must be fixed length and it must sort well, if you see what I mean. So use either some external format with 4 year YYYY followed by month MM and finally day DD, or internal format padded with leading zeroes.

Then format your search criteria the same way, so criteria and target values are compatible.

Then the following query should clinch it:

WITH SYMBOLIC ] ITEMCRITERION AND WITH SYMBOLIC ⇐ ITEMANDDATECRITERION

(I don't know if I explained that well enough.)

- Oystein -

Oystein,

Yes, you did explain it well - brilliant suggstion. Thanks.

Mark

glicks@compuserve.com

BG-Map Botanical Garden Mapping System [img]http://www.bg-map.com/bgmap.gif[/img]

Mark,

But of course there are limitations, as I have discovered with my own data. You can't do any search on two AMV fields with this strategy.

But sometimes when a new need arises you can just create another symbolic with the two fields formatted or put together in a different way.

- Oystein -

Oystein,

While your suggestions looks like it would work find for the type of search I described, how about searching for a numeric value on a certain date. For example, what if the edittable records DATE and ITEM_VALUE. How would I find PERSONS with ITEM_VALUE ] 1800 on a certain DATE?

Best wishes,

Mark

glicks@compuserve.com

BG-Map Botanical Garden Mapping System [img]http://www.bg-map.com/bgmap.gif[/img]

Oystein,

While your suggestions looks like it would work find for the type of search I described, how about searching for a numeric value on a certain date. For example, what if the edittable records DATE and ITEM_VALUE. How would I find PERSONS with ITEM_VALUE ] 1800 on a certain DATE?

Best wishes,

Mark

glicks@compuserve.com

BG-Map Botanical Garden Mapping System [img]http://www.bg-map.com/bgmap.gif[/img]

Oystein,

While your suggestions looks like it would work find for the type of search I described, how about searching for a numeric value on a certain date. For example, what if the edittable records DATE and ITEM_VALUE. How would I find PERSONS with ITEM_VALUE ] 1800 on a certain DATE?

Best wishes,

Mark

glicks@compuserve.com

BG-Map Botanical Garden Mapping System [img]http://www.bg-map.com/bgmap.gif[/img]

Mark,

Perhaps like I suggested in my last mail: Another symbolic, with the two parts put together the opposite way.

- Oystein -

Mark .. you've stumbled upon one of the absolute weaknesses of Associated Multi-Values. It is not easy to co-relate a value in one column with associated values in other columns in any easy way. Oystein's symbolic approach (which I also use in a different way) will work with one or two columns. The problem is in the programming logic. The best way to do this is to decompose the DBTABLE into a record-like structure which is stored in internal format. Then you can do a locate on any column to get its position and then check the associated values. If the same value occurs more than once, then you'll have to scan forward from there in a loop until you find another match on the primary field. If the locate fails, then the desired value is not in the table at all.

Anyway, that's how we do it.

Good Luck,

Don Miller

C3 Inc.

[/i]The best way to do this is to decompose the DBTABLE into a record-like structure which is stored in internal format.[/i]

Don, could you clarify that - perhaps using my example of DATE ITEM and VALUE.

Thanks,

Mark

glicks@compuserve.com

BG-Map Botanical Garden Mapping System [img]http://www.bg-map.com/bgmap.gif[/img]

Mark,

That method Don mentions - I have a vague feeling he's told us (the discussion lists) about it before. But I haven't been able to find the posting. (This problem with searching on AMVs is bound to raise its head in my current app, so I'd be interested in what he says.)

what if the edittable records DATE and ITEM_VALUE. How would I find PERSONS with ITEM_VALUE ] 1800 on a certain DATE?

Last time you had a symbolic ITEM:delimiter:DATE.

Now you need a symbolic DATE:delimiter:ITEM instead.

Last time I said you had to format the date so it sorts well when interpreted as a string, because your new symbolics and search criteria are strings and not integers and dates. Now this time if your items have ids of varying length they must be formatted too, i.e, format item id to fixed length padded with leading zeroes.

I think the query you need is

WITH SYMBOLIC ] DATECRITERION:delimiter:"1800" AND WITH SYMBOLIC DATECRITERION:delimiter:"1800" AND WITH SYMBOLIC DATECRITERION:delimiter:"1800" AND WITH SYMBOLIC =< DATECRITERION:delimiter:"9999"

(where "9999" is the highest possible item id (formatted to fixed length). Or any higher value of the same length, really. E.g "AAAA".)

Oh, no. This was overkill. Hope at least I got all of it right.

- Oystein -

Mark ..

Assume you have a DBTABLE with 3 columns. Column 1 is a code, Column 2 is a date, Column 3 is a quantity. You want to match the code in column 1 with a date range in column 2 or, perhaps a quantity in column 3.

TABL=GET_PROPERTY(CtrlEntID,"ARRAY")

* Iconv columns 2 / 3 to internal format and setup a pseudo record into a local variable called REC, delimited by Field Marks and Value Marks within the field marks.

K1=COUNT(REC,@VM)+(K1 # '')

* if k1 is small, just scan otherwise, you can start at the first hit if any..

FOUND=0

IF K1 ] 10 then

LOCATE Prod_Code in REC SETTING HIT THEN

   FOR I=HIT TO K1

• for OR logic, extract test values from array

      TST=REC   ;* TST could be a quantity or the search below 

could be modified to be an AND search.

      TST2=REC  ;* for OR LOGIC

      IF (TST ]= LOW_VAL AND TST 

         IF TST2 ]=LOW_VAL AND TST <= HIGH_VAL THEN ..

         FOUND=I

         I=K1   ;* to stop the scan

      END

   NEXT I

END ELSE

• no match on the product code .. error logic or whatever

END ELSE

FOR I=1 TO K1

• do the same logic as above

NEXT I

END

IF FOUND THEN ..

• do whatever you need

END ELSE

• No match

END

RETURN 1 (or zero, if you need to keep the user there)

Hope this is useful

Don Miller

Don,

Thanks for taking the time for a thourough explanation. I was hoping for a simple way to do this via RLIST, but, apparently, that's not possible.

Cheers,

Mark

glicks@compuserve.com

BG-Map Botanical Garden Mapping System [img]http://www.bg-map.com/bgmap.gif[/img]

View this thread on the Works forum...