Find on multiple fields in a portal

[obesefeline]

When I do a single-request find on two fields in a portal, the found set includes all records in the primary file for which some portal record(s) have either portal field matching its specified search value, i.e., the find seems to be doing an OR on the portal records, not an AND. Is this the way FM treats finds in portals or do I have another problem? I'm using FM 5.5.

Doug

[ernst]

Hi Doug,

I just tested this, with my version 6.x is was certainly doing AND.

Are you sure that the fields in the portal are from the relation showing in that portal?

Ernst.

[BobWeaver]

There is a sneaky way around this. Go into define fields and set the field options for the portal field in which you want to do the AND search as repeating with as many repetitions as you will have AND criteria. Then create a duplicate of your original layout but format the portal field to display all the repetitions. Use this layout when you do your search. Enter the AND criteria into different repetitions of the field in a *single* portal row. This should give you the result you want.

And, before everyone cries in alarm, "Don't use repeating fields," remember, your normal data is not repeating, only the search criteria. So, it won't create any data handling problems. Just make sure that you never display more than one repetition on your normal data entry forms.

[ernst]

Hey Bob,

This does an AND when searching in one field whilst Doug wants and AND with two fields.

(If I understand correctly...)

Ernst.

[BobWeaver]

I wasn't sure, but when Doug said he was getting an OR result, I thought he may be trying to do a search in the same field in two or more portal rows.

[obesefeline]

Say there are only two fields in a portal record, one a date field and the other a code field. In my find I'm putting >=mm/dd/yyyy in the date field and some value in the code field. I get records where either field matches its search value. I want only records where both fields match their search values.

And in response to one suggestion, I checked the fields involved and both are from the portal relationship. Indeed, all fields in th eportal are from the the portal relationship. Again, I'm using FM 5.5.

Doug