Solved

Logical AND versus OR Finds on portals in FM 5

Posted on 2009-05-07
10
640 Views
Last Modified: 2013-11-05
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
Comment
Question by:cherrylane
  • 5
  • 3
  • 2
10 Comments
 
LVL 28

Accepted Solution

by:
lesouef earned 313 total points
ID: 24330254
it should be "AND" if you type them on the same line...
which field is used to link to the related table?
0
 

Author Comment

by:cherrylane
ID: 24330560
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
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 313 total points
ID: 24331707
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
 

Author Comment

by:cherrylane
ID: 24336389
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
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 313 total points
ID: 24342863
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 9

Assisted Solution

by:jvaldes
jvaldes earned 187 total points
ID: 24348447
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
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 313 total points
ID: 24348752
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
 
LVL 9

Assisted Solution

by:jvaldes
jvaldes earned 187 total points
ID: 24351474
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
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 313 total points
ID: 24351986
> 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
 
LVL 9

Assisted Solution

by:jvaldes
jvaldes earned 187 total points
ID: 24360618
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now