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

Form drop-down list puzzle - any suggestions?

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

  • Form drop-down list puzzle - any suggestions?

    I often find I want a field on an A5 user-data-entry form to present choices populated from a lookup table where I want to restrict the choices in the drop-down list. I have never managed to get this to work in A5. Is there a reason for this or is there a better way to do it?

    Here is an elementary example A5 database attached (zip format). This example is a hypothetical purchase order for a motor vehicle reseller.

    I have the following 3 main tables:

    1. product - the vehicles for sale,
    2. color - all the possible colors across the product range (just used for color code validation),
    3. product_colour - tells me what colors a product is available in.

    For example, in the product_color table I have a Ford model T that is available in black and also in red (yes, the latter is new for 2005). 8-)

    I also have a purchase order entry form with a browse box showing the individual purchase order line items. On the purchase order line item I want to be able to record the product being purchased and the customer's color choice. So I have a drop-down list for the color populated from the product_color table and a filter on the form browse properties restricting the displayed colors to those available for the product, like this:

    Purchase_Order_Item-"Product_Id=Product_Color-"Product_Id

    But the filter doesn't work. A5 says the syntax contains an error when it doesn't, and the drop-down list of colour choices show all colours for all products.

    So I'm stumped. I would have thought A5 would handle this with aplomb.

    Does anyone know an easy solution to this problem? I want to be able to restrict the color range shown to that available for the product and not to all the colors available for all products.

    Many thanks.

  • #2
    RE: Form drop-down list puzzle - any suggestions?

    Bret

    There may be an easier way to do this but then again I did follow the help file to a point.
    First, the filter that you thought you applied to the form browse is actually not a filter. That is one of three methods to choose where you will source the browse from. I changed it to embedded.
    Second, even if you were to apply a filter to the browse it would affect the records of the Purchase_Order_Item table not the choices in the lookup table 'product_colour'.

    I followed the instructions from the following Help page.
    http://support.alphasoftware.com/alphafivehelp/index.htm#User_Manual/Displaying_Selected_Lookup_Table_Records_(Filtered_Lookups).htm

    Then to set the value of the variable
    1/ In field rules for the Purchase_Order_Item->product_Id field add the following to the 'Canwrite' event: var->ProdType=A_FIELD_VALUE

    2/On the form browse OnArrive and OnRowChange events also add code to set the same variable to the value of the Product_Id field.

    In 1 the variable gets set when you first choose a car from the list for a new record or change the car type. In 2 the variable gets updated to reflect the active row.

    Hope this helps. It was a good exercise for me as I am getting back into learning A5 after not using it for some months.


    Tim
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

    Comment


    • #3
      RE: Form drop-down list puzzle - any suggestions?

      Thanks, Tim. That's really helpful. I am working through your changes in detail.

      Comment


      • #4
        RE: Form drop-down list puzzle - any suggestions?

        Brett,

        Here is the answer to you filtered lookup. If this takes you more than 20 seconds, well?

        Please see attached.

        There is nothing else for you to change. The form is good, the browse is good, thats it.


        RegardsKeith HubertAlpha Guild MemberLondon.KHDB Management Systems
        Regards
        Keith Hubert
        Alpha Guild Member
        London.
        KHDB Management Systems
        Skype = keith.hubert


        For your day-to-day Needs, you Need an Alpha Database!

        Comment


        • #5
          RE: Form drop-down list puzzle - any suggestions?

          Ooops

          Sorry forgot attachment.

          RegardsKeith HubertAlpha Guild MemberLondon.KHDB Management Systems
          Regards
          Keith Hubert
          Alpha Guild Member
          London.
          KHDB Management Systems
          Skype = keith.hubert


          For your day-to-day Needs, you Need an Alpha Database!

          Comment


          • #6
            RE: Form drop-down list puzzle - any suggestions?

            Keith,

            I guess there was an easier way. That was my first thought of where to filter the lookup as well but used the wrong expression. I used Product_Id=Product_Id ( which not surprisingly returned all records) What you did is what I was trying to do. However I am still on my training wheels and was using the expression builder (it also keeps typos to a minimum) which only lets you choose from the Product_colour table. I really should have clued in after I used the variable in the expression which isn't available in the expression builder either. Uptill this point I had always assumed that if it wasn't available in the expression builder it wasn't a legitimate expression. Well,I quess it wasn't a waste of time coming here today. (for me atleast)

            Tim
            Tim Kiebert
            Eagle Creek Citrus
            A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

            Comment


            • #7
              RE: Form drop-down list puzzle - any suggestions?

              Bret, apologies for sending you up the wrong tree.

              Tim
              Tim Kiebert
              Eagle Creek Citrus
              A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

              Comment


              • #8
                RE: Form drop-down list puzzle - any suggestions?

                Hi Tim. No probs. I have learnt something new so it wasn't wasted effort. Thanks again.

                Comment


                • #9
                  RE: Form drop-down list puzzle - any suggestions?

                  Really! What a stunning revelation for me. Like Tim I had always assumed because the expression builder only shows the fields of the table being looked up I could not reference fields in the source table, which is annoying but I had always put up with it as just being another of those A5 foibles that I would prefer wasn't there. This revellation opens up all manner of new possibilities. Thanks Keith. That's something really interesting I have learnt today too.

                  Comment


                  • #10
                    RE: Form drop-down list puzzle - any suggestions?

                    Hi Guys,

                    Glad to be of help.

                    Goes to show how Alpha has moved on. What Tim was showing us was developed for V4. There is an example in www.learnalpha.com .

                    There are many times that a reference is used in the expression builder that cannot be seen directlybut can still be evaluated. A Case statement as an example.

                    Just to spice things up a little, first the Colours can be put into ascending order and then you can make the lookup field auto popup on entrance.

                    RegardsKeith HubertAlpha Guild MemberLondon.KHDB Management Systems
                    Regards
                    Keith Hubert
                    Alpha Guild Member
                    London.
                    KHDB Management Systems
                    Skype = keith.hubert


                    For your day-to-day Needs, you Need an Alpha Database!

                    Comment

                    Working...
                    X