Solved

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

Posted on 2008-10-14
7
557 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 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
webwyzsystems: Thanks, I won't have access to the db until later today but will try this then.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:hncnscr
Comment Utility
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
Comment Utility
"goto record /next" must be the last step in the loop
0
 

Author Comment

by:hncnscr
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

11 Experts available now in Live!

Get 1:1 Help Now