Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Use of calculated fields in mapped tables

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Use of calculated fields in mapped tables

    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
    Pat Bremkamp
    MindKicks Consulting

  • #2
    Re: Use of calculated fields in mapped tables

    a5_getArgumentValue()?
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

    Comment


    • #3
      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
      Pat Bremkamp
      MindKicks Consulting

      Comment


      • #4
        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
        Code:
         
        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.
        Mike
        __________________________________________
        It is only when we forget all our learning that we begin to know.
        It's not what you look at that matters, it's what you see.
        Henry David Thoreau
        __________________________________________



        Comment


        • #5
          Re: Use of calculated fields in mapped tables

          Originally posted by Pat Bremkamp View Post
          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
          Al Buchholz
          Bookwood Systems, LTD
          Weekly QReportBuilder Webinars Thursday 1 pm CST

          Occam's Razor - KISS
          Normalize till it hurts - De-normalize till it works.
          Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
          When we triage a problem it is much easier to read sample systems than to read a mind.

          Comment


          • #6
            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
            Pat Bremkamp
            MindKicks Consulting

            Comment


            • #7
              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

              Comment


              • #8
                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

                Comment


                • #9
                  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
                  Pat Bremkamp
                  MindKicks Consulting

                  Comment


                  • #10
                    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

                    Comment


                    • #11
                      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
                      Pat Bremkamp
                      MindKicks Consulting

                      Comment


                      • #12
                        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
                        PHP Code:
                        }
                        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

                        Comment


                        • #13
                          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
                          Pat Bremkamp
                          MindKicks Consulting

                          Comment

                          Working...
                          X