Jump to content

Limiting number of portal results


James Gill

This topic is 4964 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by Guest
Link to comment
Share on other sites

  • 2 weeks later...
  • 2 weeks later...

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 by Guest
Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 1 month later...

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

prefilter.gif

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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.).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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)?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 weeks later...

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

... 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 by Guest
Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

This topic is 4964 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.