Hi everyone,

I am facing a bit of a dilema with a solution I am working on. I essentially have a set of jobs that need to be completed, and have used a formula in a script to work out the distance between a Starting point A and an end point B. It then works out the intermediate distance between Point A and the job - lets say point M - and stores that in a field. It does the same for the distance between point M and point B, and stores that in a seperate field.

It then excludes records outside certain distance ranges, and sorts them by incrementing distance from point A.

My problem is that between 2 and 3 users are constantly going to be using this solution to sort jobs and create routes. As a result, the fields will be constantly changing, so the distances will never be correct - and there is likely to be some unpredictable behaviour. I can't, however, use global fields (which are user specific), as these allocate one value for all records.

All suggestions would be greatly appreciated. If you require further information, or I haven't explained the problem clearly, please let me know!

Thanks in advance,

Jason V.

Do you need to keep the routes stored?

Maybe you can use globals to evaluate the routes and then script them to be stored in a related table.

Edited December 1, 200817 yr by Guest

This is not quite clear. Are the points A and B user-specific? If yes, why cannot the distance be an unstored calculation?

At the outset, the route would not have to be stored. However, the user will be able to select certain jobs on the route which will transfer them to a seperate table (transfer the job ID).

The points A and B are user specific. Each user will enter these points, say perhaps 2 postcodes, and it will give you an overall distance between the two (using the Haversine formula) and then evaluate the individual distance between each of points A and B and the job points.

Say for example you want a route going from SL3 near London to M1 in manchester. If you had a job in Birmingham postcode B1, it would give you an overall distance between SL3 and M1, and then a distance SL3 - B1 and B1 - M1.

Do you reckon I could make all these distances unstored calculation fields? The formula is quite long... I am wondering how feasible it would be to have that as a calculation field?

Thanks for the replies!

I don't think the length of the formula is necessarily an indication of its complexity. The Haversine formula seems to me a rather simple one, actually.

In any case, I don't see that you have much choice here - unless you want to import the eligible jobs into a holding table (where they would be marked by the route ID).

How many jobs are there anyway? Or rather, how many active jobs?

Edited December 1, 200817 yr by Guest

Your most certainly right. I had a go and it worked quite well, having it as unstored calculations. I underestimated the power of these fields, thinking it would slow it down too much.

Thanks very much for your help (there would be around 1000 jobs at any one time, but these would never be viewed altogether. They are filtered beforehand - by non calculation fields - so you are only looking at calculating for between 10 and 100 jobs at a time. I just ran a test on about 25 and it didn't slow it down the least bit).

I *think* that any slowdown would occur at the point of filtering and sorting the portal. After that, the join results would be cached - so the overall effect shouldn't be much different than a scripted process.