Where's my OR operator! :-D

[liltbrockie]

Ok I know theres a way round this but i need help quickly!.. see the attached file....

I'm basically trying to make a portal in the orders table to display customer address to choose from based on EITHER the postcode OR the surname... i can make it work with one on its own .. or the two combined but I cant have it look at the postcode OR the surname OR the two combined!

Any help will be much appreciated.

test_customerss.zip

[Ocean West]

Hope this helps... took a few liberties to tidy up the graph a bit.

test_customerss.fp7.zip

[liltbrockie]

Thanks Dean.. not really what I was hoping for i dont think... what im trying to do is be able to input either the customer surname OR the postcode and have matching records come up for either in the portal! AND having matches for both surname and postcode come up too!

[Simon K]

in your customers table create a text global - put it on the layout for users to enter the post code or name of customer

in your customers table create a calc text field with the calc set to concatenate post code to name with a carriage return between ie name & ¶ & post code

create a new table occurence of the customers table and create a self join based on the global = the new concat field

base your portal on your new table occurence

NB this will only work with whole names/post codes

HTH

[liltbrockie]

hmm interesting ... so it is Impossible to have a portal Display records from either one match or another match or a match on both fields?

[Simon K]

The technique relies on the "carriage return/pilcrow" - Filemaker treats such values in a special way and in effect gives a join for each value in the field (that is separated by a return)

- thus giving your return results for just the name, just the post code or indeed both (ie if your users were to type ["king" return "smith" return "so45"] into the global then the portal would show matches for any/all of the entires ie where name = king or name = smith or post code = so45

NB this is an equal join that takes advantage of value lists within fields - it wont work on "begins with" etc - which would need to be a scripted find

[liltbrockie]

ahh ok I see ... now we are getting somewhere!

BUT

If I do "matt" carriage return "RH2 8YU" I want JUST those matches to come up.. i dont want all the matts and all the RH2 8YU.

I want them to come up if i put them in on their own...

BUT

if i put in both of them I want only records that have them both in to come up.

[Simon K]

It really depends on how you want the users to use the solution (and the volume of data - see below) ...

if you want to use the global/relationship/portal approach (as we have been discussing) you could expand the calculation of the "keywords" - to, say, include the first 3 letters of name concat'ed with first 3 letters of post code (ie so that the user could type in "mat rh2" and get a match - this is where the volume of data might affect the users ability to get a single match

you could still add this calc in to "name" in full and "post code" in full - all separated with carriage returns ie to give you returns for "mat rh2" "matthew" "Rh21 5ty"

Alternatively, (although it's more work) it may be better to do a scripted find - based on user entered values - in which case the partial "begins with" is just one of the operators you could use - combine that with all the omit/multiple requests and that is probably more effective...

But you will have to build a separate pop up window, return the value to the calling window and process from there - perhaps a more "normal" but slightly slower yet more flexible approach

It really depends on what you are really trying to do and what your user expectations are