• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 653
  • Last Modified:

Logical AND versus OR Finds on portals in FM 5

In FileMaker 5 I have a portal that contains log (text) data about employee activity that is being recorded in the database. The portal also contains an Employee ID, Employee Name, and a date field. The idea is that an Admin wants to be able to search this portal on Employee and a date range (e.g. Bob Blah and >= 5/1/09). The problem is that by default FM is doing a logical OR search (Bob Blah OR date >= 5/1/09 instead of a logical AND search (Bob Blah AND date >= 5/1/09).

Does anyone know how to force FM to perform a logical AND search when finding related records in a portal? No matter how I try this search I always get OR results and nothing I do produced AND results.

Please help!

Thank you!
0
cherrylane
Asked:
cherrylane
  • 5
  • 3
  • 2
8 Solutions
 
lesouefCommented:
it should be "AND" if you type them on the same line...
which field is used to link to the related table?
0
 
cherrylaneAuthor Commented:
They are different fields (Employee ID [type: number] and Date [type: date]) I can't make it a calculation because the search query won't parse correctly that way (e.g. Employee 50 >= 4/23/09 won't return the correct results).
0
 
lesouefCommented:
this is not what I meant. I wanted to know if any of the 2 fields used to find records was also used to link both tables?
I'll try to figure out what happens too-morrow. Do you have a sample file I can get?
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
cherrylaneAuthor Commented:
Oh I see. No, the 2 fields used to find records are not the fields the relationship is based on. The relationship between the two files is on a unique ID from the master file.

I'll see if I can provide some examples shorlty.
0
 
lesouefCommented:
just tested you pb. you thought the wrong way, let me put try to explain.
when you search from for sub-records from the parent table, fm search records in the current table for which there are sub-records matching your pattern; once found, it shows all sub-records of the found records, logic. conclusion, you cannot search in a portal unless you use a script to change the relationship on the fly. but with only 1 field in fm6, not easy. the only workaound I know is to use a calc field like
searchID=ID & search_expression
do the same in the related table, make a relationship on this new field, which will display records for which the ID and the search expression will match. In your case, you should use 3 fields actually, the third one being calculated on "is the date range true", as fm6 does not accept multi-field and non equal relationships (you would be in a much better shape with fm7+ for this, but I am not here to sell fm).
so that's another calc field in the related table to find the current record is within the date range of the parent table. starts being complex for what it does. You'd better search in the sub table directly. another way to simplify this date range search is to reduce the search date criteria to year/month/week/day, as this eliminates the "date_range true" check by making extra fields calculated after the date which are not usable in a relationship.
0
 
jvaldesCommented:
I recommend that you create a field which I usually call SHOW in the portal records then create a script to compute if show should be set to one (1) then relate the portal by show =1. This doesn't slow down the database the way a calculated field would and would provide the same capability. This approach is needed if you have hundreds of records.

So if you have a record that meets the criteria you want you set SHOW to 1. Which will make them visible in the portal
0
 
lesouefCommented:
there is a drawback to this way Juan, if 2 concurrent users do the same search, they will interact on each other if the show field is stored.
if you make the show field a calc one (non stored), it can't be used for a relationship. so with a date range, I don't know the perfect way; this is why I had come up with a year/month/day filter which can use a stored field
0
 
jvaldesCommented:
I have used a repeating field to store the show and have used the user number (computed serial renewed everyday...) to define the user specific show in the field. When you need to consider multiple users at the same time.  I agree that this requires a bit more coding but it enhances the speed of the application. If you have in excess of 500 items on the list a displayed portal that has a calculated show value is slow. So I think it depends on the application scale...
0
 
lesouefCommented:
> computed serial renewed
woh, that is super smart!! though in theory you may still exceed the amont of repetitions if a user logins several times a day, the trick is excellent. You could also allocate a repetition number to each user upon account creation if you maintain a user table (which I do in all my apps). excellent, I had never thought of this. and I confirm that using search to filter records in a portal is slow.
0
 
jvaldesCommented:
I have made a number of apps that work together in a medium sized company that uses the employee number as the index for the repeating field. This is a significant limitation in filemaker's multiuser environment and that is my workaround...

Where there is no employee number an approach you can use is to reset every night or reset on a specific count. Remember that these uses are not mission critical and if the user gets wiped they will just execute the search again and nothing will go wrong.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now