Solved

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

Posted on 2008-10-14
7
561 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
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
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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