Use of calculated fields in mapped tables

[Pat Bremkamp]

A question for V9 users...

One shortfall of V8 is you can use arguments in filters in grid components, but you can't use them in calculated fields. The help says in Version 9, you can create calculated fields in mapped tables and use them just like any other field.

I have an app where I take either longitude and latitude or, if none, zip code, and calculate the distance to all customers, sort from closest to farthest and display the results in a table with a link to the customer page.

Because of no arguments in calculated fields, I couldn't use a component. I ended up doing everything in Xbasic and displaying the results in an html table. Took me a couple days (OK, I'm slow!).

Seems like mapped tables are like a view with built in multitable calculated fields. With that capability, I could have used a grid and done the whole thing in a couple hours. I know because I first built one expecting to use arguments, then discovered I couldn't.

I'm building a case for my customers to move to V9, so I'd like to ask for responses from people using mapped tables on what their experience has been.

Are they quick to set up? Stable? Reliable? Are there restrictions on the kinds of calculated fields you can create? Restrictions on the number of tables that can be mapped? How do they work with grid components? That sort of thing.

Thanks,
Pat

[Al Buchholz]

Re: Use of calculated fields in mapped tables

a5_getArgumentValue()?

[Pat Bremkamp]

Re: Use of calculated fields in mapped tables

Art,

Is that a function? It's not in Web help. Or, is it a suggestion?

Have you used that in a calculated field?

Pat

[MikeC]

Re: Use of calculated fields in mapped tables

Pat,
Put a5_getArgumentValue() into the interactive editor and you will see that it is a function but must just be undocumented is all...

It will show

 
a5_getArgumentValue(C argName) 
Used in report writer to extract values of arguments at runtime so that they can be included in the report itself.

[Al Buchholz]

Re: Use of calculated fields in mapped tables

Art,

Is that a function? It's not in Web help. Or, is it a suggestion?

Have you used that in a calculated field?

Pat

Like Mike said....

and who's Art? :D

[Pat Bremkamp]

Re: Use of calculated fields in mapped tables

Thanks, Mike and Al,

Art is what happens when my wife slips me decaf...

That was a good suggestion, but it didn't work in a grid in my testing. If I put in a numeric value, I got an error. If I put in a character value, no error, but also no value. However, I was using the :argTest identifier. Perhaps, like calc-> or var-> there is a variable space that needs to be referenced?

Anyway, back to my original question...does anyone have mapped tables with multitable calculated fields experience in a grid they could share?

Pat

[quinster]

Re: Use of calculated fields in mapped tables

Pat,

I've done a little. I have a Quickbooks export of an audit trail. As is, the data is useless, because much of the identifying data is only shown in the first record of the transaction. Also, if a transaction has been modified more than once, each prior transaction shows up only as "Prior", whereas I would like to show each prior transaction with a different label - "Prior1", "Prior2", etc.

I decided that a mapped calculated field would be a good way to see how my expressions that I use in update operation would evaluate to. The previous() function shows what is in the prior record. If I define a query on the mapped table, the function refers to the previous record in the query, not the entire table.

Seems like a good way to test how a formula evaluates.

Dan

[richard2007]

Re: Use of calculated fields in mapped tables

msg for pat,

did you manage to get something for calculating distances. this is exactly what i need.

thanks

richard

[Pat Bremkamp]

Re: Use of calculated fields in mapped tables

Richard,

Yes. The calculation I'm using calculates the distance between two points (in miles) given their longitude and latitude (in decimal form).

I found the formula by googling. One site showed a php script for it, but another just showed the formula. I picked the formula rewrote it in Alpha Xbasic. If works fine.

You would probably need one with different constants to get kilometers???

Pat

[richard2007]

Re: Use of calculated fields in mapped tables

thanks pat,

ill have a look. what i am trying to achieve is to allow a user to perform a search on products within a certain mileage of their home (they can filter the mileage in increments of say 10).

then they can choose the product they want based upon distance or other criteria.

will the method you found achieve this?

thanks

[Pat Bremkamp]

Re: Use of calculated fields in mapped tables

Yes, that's what my routine does.

My customer (INeedWheels.com) hosts small auto dealer inventories and she has a number of auto dealers in Pennsylvania, New York and Florida. So, I ask for the user's zip code (postal code) and then display all dealers within 300 miles, sorted from nearest to furthest.

I was going to offer a choice of cutoff distances (an easy add), but the lists were not too long, so just cut them off at 300 miles.

I also found on line a table of zip codes with the longitude and latitude of approximately the center, so I use that for the conversion.

With GPS becoming more popular, I'm thinking of letting users enter that as an alternative.

Pat

[richard2007]

Re: Use of calculated fields in mapped tables

pat,

could i ask for a little help. i have a database of uk postcodes with longitute and latitude values.

i have also found a php script to calculate the distance between the two, but i have no idea on where to even start in converting this to xbasic.

i get that i need to run a script which will take the postcode from the user running the query, and then take the postcode value from the product sale locations.

match these with the values in the postcode table to extract the longitude and latitude values.

i am struggling to understand the next pocess - how to make alpha use these long and lattitude values for what they are and not just numbers and then converting the whole thing to xbasic.

this is the php script to get the lng and lat values

} 
function calc_postcode_seperation($pcodeA,$pcodeB)  
{  
// PCODE A  
$result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$pcodeA' LIMIT 1");  
$row=mysql_fetch_array($result);  
$gridn[0]=$row[Grid_N];  
$gride[0]=$row[Grid_E];  
// PCODE B  
$result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$pcodeB' LIMIT 1");  
$row=mysql_fetch_array($result);  
$gridn[1]=$row[Grid_N];  
$gride[1]=$row[Grid_E];  
// TAKE GRID REFS FROM EACH OTHER.  
$distance_n=$gridn[0]-$gridn[1];  
$distance_e=$gride[0]-$gride[1];  
// CALCULATE THE DISTANCE BETWEEN THE TWO POINTS  
$hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e));  
$text.='Distance between '.$pcodeA.' and '.$pcodeB.' is: '.round($hypot/1000,2).'kms';  
return $text;  
}  
echo calc_postcode_seperation('LE7','BD1');  
?>  

i will need to convert to use a .dbf table.

i want to run this from a website only. id appreciate any help you can give.

thanks again

richard

[Pat Bremkamp]

Re: Use of calculated fields in mapped tables

Richard,

Looks like that formula is designed to work in small areas...there is no trig to account for the curvature of the earth. Also, it is using a MySQL table.

Does this fit your needs...that is it is only in England, so the approximations are close enough, and you will use a MySQL data source?

Anyway, if you'll send me a private message with your email, I'll email back what I used.

Pat