Limiting number of portal results

[James Gill]

I have a portal that I am displaying on a layout that has close to (and growing) 3,000 records. I want to only display the first 100 found records of a related set in this portal, however, using the new filter portal command in FMP 11 seems to only limit the number of portal records displayed to the user, not how many records FMP applies calculations to, and displaying a large found set in a portal takes a very long time on slower machines.

How would I go about limiting the number of related records that not only are displayed in a portal, but also have calculations applied to them via the portal?

Edited May 11, 2010 by Guest

[mr_vodka]

...but also have calculations applied to them via the portal?

What do you mean by this?

[James Gill]

Each record in the portal displays the distance to a remote site from the main record. Because there are so many records currently in the portal any time a user clicks it, it takes about 3-5 seconds to load (up from almost instant when we first implemented it.) I've tried applying a filter to the portal in Filemaker 11 using consecutive number to 100 (thereby limiting the number of related records to anything <100) but when I click the portal I still get a "Sorting 3,000 related records" that flashes up.

What I meant by the above post is that FMP 11 still appears to be applying all of the related calculations to even those records that are filtered.

Edited June 1, 2010 by Guest

[mr_vodka]

Try filtering the relationship instead of the portal. When a filter is applied to the portal, each record has to be evaluated like an RLA scenario.

That being said, FM should be able to handle 3000 records in a portal without really killing performance. What are you displaying in this portal? Aggregates?

[Jack Rodgers]

Sorts on calculated fields are slower than sorts on fields that are not calculated. The more relationships to evaluate, the slower the sort. Storing or not storing the calculation can also affect the time. Sometimes its only barely noticeable while other times...

If a designer tries hard enough they can drive any database to its knees or discover all kinds of ways to crash it. I am a founding member and invite others to apply.

A list view is so much more flexible than a portal I reserve portals for small stuff.

Sorting at the TO level, as mentioned above, is faster.

[James Gill]

Sorry to resurrect this post, but I'm still having problems with limiting the number of related records that appear in a portal. I definitely want to use a filter at the relationship level but now I'm running into problems with how to accomplish this.

I have created a field in the parent record that I can enter a number into that represented a maximum allowable distance for the portal to display. This field was then set up in the relationship to filter based upon the calculated distance from the parent record, thereby limiting the number of displayed results in the portal to whatever distance I specified.

Unfortunately this does not work. I'm not sure if it's because I'm trying to filter based upon a calculated value or if it's something else I've done.

[comment]

It seems you are trying to "filter" the relationship by an unstored calculation in the child table - this, of course, cannot work. Please provide more details.

[James Gill]

As usual Comment, you're correct. The calculation that I am using is the distance in miles from the parent record to the child record. (In this case they are both locations). This is also an unstored calculation.

Is there a way to filter the portal using a sort of get(Recordnumber) command through a portal relationship to only display "x" number of records? I'd like to limit the number of displayed records via the portal to a definable preference available to my users but thus far haven't been able to figure out how to accomplish this.

[Vaughan]

One way is to generate a list of record ids, get the first n of them, then use this as a key for another portal.

the value for "n" could even be stored in a field somewhere.

[comment]

This has come up before. My suggestion is to calculate a bounding box in the parent record, and define the relationship as "within the box". This is using purely stored and indexable data (and it may be good enough to leave it at that).

If you want to get more accurate, then filter the portal by the actual distance calculation.

[James Gill]

How would I go about doing that Comment? If I could give my users the option to set their preference of displayed distance (Say 10 miles, 50 miles, 100 miles) and then have the portal relationship only display those records that fell within that distance, that would be ideal. It would not only make the data more presentable, but also speed up sorting of records and the loading of the related data, which is my original goal.

What I was trying to do at first was to use the actual distance calculation field on the child side of the relationship and and then a number field on the parent record as the distance preference. Unfortunately this didn't work as the calculation isn't a stored calculation.

That's when I hatched my idea of using some sort of get(RecordNumber) command that would count each related record, starting at 1, and limit the results via the relationship to the first one hundred records. This didn't work as, as far as I'm aware, there is no way to count records via a portal relationship.

Edit: Whoops, I missed a crucial part of the "filter by distance" part of you reply. I can very easily filter the portal by distance. The problem is that apparently Filemaker still tries to apply calculations to all of the records in the relationship, even the ones that are filtered out of the portal. Since my main goal is to speed up the database solution, while filtering via the portal itself would appear to work, it really doesn't accomplish what I need it to :

Edited August 13, 2010 by Guest

[comment]

I missed a crucial part of the "filter by distance" part of you reply.

No, the crucial part here is reducing the amount of related records, BEFORE filtering the portal. In the attached picture, the box (actually a barrel) bounded by the blue dashed lines represents the area where related records would reside, and the red circle represents the portal filter.

[James Gill]

I definitely get the concept, but this is what I was trying to accomplish by using the calculated distance to try and limit the number of related records in the portal relationship. Unfortunately this doesn't appear to work because the calculation is not a stored calculation (and cannot be, as it uses other unstored calculations to determine the distance). Maybe I should better describe the schema of the data (this was not designed by me);

The parent record is where the primary starting address is stored. The child records are where the secondary destination address is stored and is also where the distance from the primary address is stored in the calculation field.

Each time a user browses to a new parent record or changes the parent record address, a script is run that geocodes the lat and long of the new parent record address. The new address is then calculated against the existing child records address and returns with a distance.

In my opinion, it's understandable that this portal runs extremely slow, especially when it's doing this for 2,500+ related records every time the user changes records. If there is a better way to accomplish this I'm all ears. I would imagine creating a field that stored the result of the calculated field and is automatically populated with the new distance would work, but I question whether this technique would be any quicker than what's currently being used...

[comment]

I am afraid you are still missing the point. To calculate the distance between 2 points, you need the coordinates of both. This, obviously, cannot be stored (unless you pre-calculate all possible distances - with 2,500 locations, that would be a table with over 6 million records).

The same is true when determining whether location B is within radius r from location A. This calculation also requires the coordinates of both points, and cannot be stored.

However, in order to determine whether point B is within the bounding box in the above drawing, you need to calculate only the two latitudes and the two longitudes that are r away from the given point A (the parent record) - and compare them with the STORED coordinates of point B (and C, D, E, etc.).

[Vaughan]

Comment: so what you're suggesting for the solution is the parent point A record has to calculate a multi-key field that contains all the possible point Bs that are within the bounding box or radius. This multi-key is then used for the portal relationship.

Thinking some more, rather than calculate "all possible" point Bs it may suffice to use a multi predicate relationship with > and < to calculate the ranges. This may be quicker. (I thinks some more: yes I see now why you have a bounding *box*: this would make calculating that lat/lon range much simpler than a circle and would probably be sufficient for most purposes.)

Of course, it may be that pre-computing all the distances between the points is also a feasible solution. It need only be done once (as long as all the points are known in advance).

Thanks.

[comment]

to calculate a multi-key field that contains all the possible point Bs that are within the bounding box

No, that would be insane. All you need are 4 calculation fields and a relationship:

Parent::cMinLat < Child::Lat

AND

Parent::cMaxLat > Child::Lat

AND

Parent::cMinLon < Child::Lon

AND

Parent::cMaxLon > Child::Lon

At this point, you'll have about 20% false positive matches (though none of them farther than 140% of the specified radius), and you can use portal filtering to remove them.

Edited August 13, 2010 by Guest

[Vaughan]

Ok yes I see. Excellent.

Would the portal filtering work on an unstored calc field in the related table that calculates the distance from the point B to Point A (where point A is stored in a global)?

[comment]

Would the portal filtering work on an unstored calc field in the related table

Yes - that's the advantage (and the disadvantage) of portal filtering. You don't actually need the calculation field, because you can do the math in the filter itself.

[James Gill]

Ok, so I've let this topic sit in my mind for a couple of weeks and interestingly I came up with the same solution that comment mentioned two posts above. Knowing the coordinates of the child record I can define a maximum distance for each user. Unfortunately, I have yet to find a good way to do this. (Not a math major here.)

I've found a way to calculate an end point from a starting point given a distance, but the formulas aren't adding up. On this website http://www.movable-type.co.uk/scripts/latlong.html there is a section titled "Destination" that will give me what I need; a destination point given a known bearing, distance, and point. Unfortunately I'm having a hard time deciphering the formula that they've presented. This is also the only site I've found that tells me how to do this so far.

Does anybody else have a good source?

[comment]

I don't have time to play with this now, but I think the formula can be simplifed since you are only moving orthogonally.

In fact, if all your locations are roughly in the same area, you could just add some constant for each mile/km of the desired radius* - after all, this is merely a first approximation to be refined later by the portal filter.

---

(*) see the table at:

http://en.wikipedia.org/wiki/Longitude#Degree_length

[Vaughan]

Look on Brian Dunning'seb site, I put together a custom function that works out latlong.

[James Gill]

I'm going to upload a sample file with the calculations I've figured out for now and hopefully you can see what I'm trying to accomplish. It's not completed yet, I'm having trouble figuring out the Min Longitude and the Max Longitude is going the wrong direction, but the two Latitude calcs are working great. I'm also going to include an excel file that pointed me in the right direction to create these calcs.

Edit: Well apparently you can't upload .fp7 files anymore?

Edit2: Welp I'm a dummy

GPS_Stuff.zip

Edited August 25, 2010 by Guest

[James Gill]

Look on Brian Dunning'seb site, I put together a custom function that works out latlong.

Cool, thanks for posting this. I'll check this out tomorrow as I've been working way to long on this today. Just a quick glance at this but it doesn't appear to be exactly what I'm looking for as this would have to be a unstored calculation to correctly calculate distance from the parent record to the child record and you can't create a relationship based upon an unstored calc.

[Vaughan]

... this would have to be a unstored calculation to correctly calculate distance from the parent record to the child record and you can't create a relationship based upon an unstored calc.

Yes. That's what Comment's box-and-circle method that used the multiple-pedicate relationship is all about. The unstored calculations have to be in the "left" side of the relationship, while all the fields on the "right" side must be stored.

Comments method is about calculating, based on the selected point, the lats and lons that would fit into a square whose side is the distance in length. This would need to be unstored. However the unstored Lat and Lon calculated is on the left side so it will work.

In this case the cf I posted probably won't be of much use. It's probably not even necessary because the math to work out the "box" of lats and lons would be pretty simple (the only complexity being working around the equator and the date line, but that probably wont affect you anyway).

Edited August 25, 2010 by Guest

[Vaughan]

Another way of looking at it:

One way is to have a starting point in the parent record, ask the child records to calculate how far they are from the starting point, and only show those that are within the desired distance. This won't work in FMP because the distance in the child records cannot be stored.

The other way is to turn the question around so the parent table is doing all the calculations: the parent record gets the starting point and the distance, and works out the points that fit within. The relationship then selects the child records that fit within the box.

Edited August 25, 2010 by Guest

[comment]

I haven't really tested this, but try:

Lat ± Degrees (  d / r )

Lon ± Degrees ( Asin ( Sin ( d / r ) / Cos ( Radians ( Lat ) ) ) )

Comments method is about calculating, based on the selected point, the lats and lons that would fit into a square whose side is the distance in length.

The side of the square is actually two times the distance, I think.

[Vaughan]

Yeah, it's that radius vs diameter thing.

[James Gill]

Comment I tried that formula but I must be doing something wrong because it's not working. Math isn't my strongest point...

Anyway, I think I know what's going on with my calculations. My latitude calc is working fine, but the long formula is all wonky. I'm was using the ATAN2 custom function that I got from Brian Dunning's website, but X and Y are reversed to be Y and X. Would this effect my result?

[comment]

I must be doing something wrong because it's not working.

Describe "not working".

I'm was using the ATAN2 custom function that I got from Brian Dunning's website, but X and Y are reversed to be Y and X. Would this effect my result?

Yes.

[James Gill]

I'm really stuck with this. The ATAN2 CF on Brian Dunnings website definitely returns a different value than the ATAN2 function of Excel. Switching the two numbers of ARCTAN2 doesn't work (not that I expected it to.) Not having ever taken higher math, unless somebody here can tell me what's wrong with the CF I think I'm stuck... :)

Edited August 25, 2010 by Guest

[comment]

I think you are making this harder than it needs to be. Not only that, the formula you are trying to use will not give you an exactly correct result (not that the difference is significant, but I thought it worth mentioning)

If you travel 100 km east from a given point, you will end up at a latitude that's different from the latitude of the origin (you can use the calculator on that site to verify this).

OTOH, the sides of the "bounding box" run precisely on meridians and circles of latitude; IOW, the top left corner of the box, the midpoint touching the circle and the top right corner all have the same latitude.

[James Gill]

Ya, that's one of the things that was sort of strange about that formula. OTOH, when I checked the distance between the two points it was something like 1,000 feet which, for me, is more than accurate enough.

The concept doesn't seem too hard, it's just never having messed with this sort of stuff is throwing me.

The problem is that the source spreadsheet for the formulas work great, but as soon as I plug it into Filemaker using the same formulas it starts spitting out strange results. I do think it's a result of the ACAN2 CF whose X and Y's are reversed. I've searched high and low but I can't find any other ACAN2 formulas that would work with Filemaker.

At this point the ONLY one that's not working for me is calculating the bearing due West (or 270 degrees). Both the North, South, and East points are working perfectly.

[James Gill]

I haven't really tested this, but try:

Lat ± Degrees (  d / r )

Lon ± Degrees ( Asin ( Sin ( d / r ) / Cos ( Radians ( Lat ) ) ) )

I'm trying this now, but what exactly am I supposed to get? I've plugged in the starting Lat and Lon, along with the Distance and Radius, but I'm getting strange results.

This is what the map looks like with two points that are 100km apart.

http://www.gpsvisualizer.com/display/data/1282776563-14664-206.169.27.14.html

Also, since it's been a while since math, what does the ± indicate?

[comment]

You need to calculate 4 points; the "±" indicates subtract for Min and add for Max, i.e.

MinLat =

Lat - Degrees ( d / r )

MaxLat =

Lat + Degrees ( d / r )

[James Gill]

You need to calculate 4 points; the "±" indicates subtract for Min and add for Max, i.e.

MinLat =

Lat - Degrees ( d / r )

MaxLat =

Lat + Degrees ( d / r )

You brilliant bastard! This works perfect now. You're right, I was definitely making it more complex than it needed to be. I wish I could buy you an internet beer or something.

Edited August 25, 2010 by Guest