Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Filemaker Pro - Convert Field (Multiple Occurrences) from List of Records to Single Searchable Field

Posted on 2008-10-14
7
Medium Priority
?
567 Views
Last Modified: 2012-08-13
Pertinent Table Info (Pertinent Fields):
Property (prID, prName)
Events (evID, evStatus, evBeginDate, evEndDate, prID, prName)

Properties can be rented by the week .  For every week in a given season a property has an event with a start date and end date and a status.  Status can be avail or unavail.

Users want to do a search for a date range, say June 1 - June 30, and get a list of properties that are available.  With a traditional search, each property can potentially show up 4 times.  I want to give them just a list of properties that meet the search with no duplication, but I want the list to be in Browse mode, so I can provide a list of clickable buttons to go with each property, i.e. go to just this property and look at the month of June.

What I've tried:
I've written a report that just shows the single list of properties.  Because it's a report I only get the single list if I display in Preview Mode but can't add any clickable buttons, if I display in Browse mode, I'm back to a bunch of records.

I'm very familiar with scripting and am happy to create a script that dumps the necessary info into a temporary table but I cant seem to get to the point that each property appears only once.  

Any help is appreciated.
0
Comment
Question by:hncnscr
  • 3
  • 3
7 Comments
 
LVL 10

Accepted Solution

by:
webwyzsystems earned 2000 total points
ID: 22713655
Here's a looping script that quickly rips thru the found set, discarding all multiple entries for the same property. please excuse the "pidgen" filemaker!

oh yeah...You will need a tempGlobal field to store the propertyID

SET FIELD (tempGlobal;"")
GO TO RECORD FIRST
LOOP
IF (tempGlobal=prID) {
       omit Record
       go to record(previous) // we do this to prevent skipping over a record
}
else {
       tempGlobal=prID
     }
go to record (Next;exit after last)
END LOOP

I think that'll work for you....but check the exit condition for your data set, you might need to tweak it.


0
 
LVL 28

Expert Comment

by:lesouef
ID: 22714901
the global field can be replaced by a variable.
the avove solution will work, but you would have avoided the problem I think by having a table for houses, and another for rented periods; you wouls have don the search in the perriods from within the houses table.
0
 

Author Comment

by:hncnscr
ID: 22719474
webwyzsystems: Thanks, I won't have access to the db until later today but will try this then.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:hncnscr
ID: 22760405
Ok, I didn't get access to the database until this weekend.

webwyzsystems: I basically followed your suggestions and came up with the following script(s) attached as png because you can't copy/past a fmp script.

The main problem seems to be where to place Go to Next, Exit after Last.  

As part of the If statement I lose the first record in the group, i.e. all instances of that record are deleted

As part of the loop but not part of the If statement I lose the last record, i.e. all instances of that record are deleted.

What am I doing wrong?



Keep-Last-Record-Lose-1st.png
Keep-1st-Record-Lose-Last.png
0
 
LVL 28

Expert Comment

by:lesouef
ID: 22764617
"goto record /next" must be the last step in the loop
0
 

Author Comment

by:hncnscr
ID: 22765946
lesouef:

When I do that the last property go away entirely.

For example:  If I search the event records for June 1 - June 30 and three properties meet the criteria for one or more periods I start off with a list like this

Prop1
Prop1
Prop2
Prop2
Prop2
Prop3
Prop3
Prop3

With Go Next last in the loop Prop 3 gets deleted entirely.  When I look at the tempGlobal field its set to Prop2 after the script has run.

 I took just the loop steps and put them into a script and If I manually run it through the records it works fine but, of course, I know when to stop.  I know there's something wrong just not sure what.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 22799755
that's because you exit too soon, that last record is not compared, therefore deleted.
so put "next record" at the beginning of the loop, and remove the one just before the loop
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Loops Section Overview
Suggested Courses
Course of the Month13 days, 21 hours left to enroll

580 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