Alpha Video Training
Results 1 to 10 of 10

Thread: Form drop-down list puzzle - any suggestions?

  1. #1
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default 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. #2
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default 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

  3. #3
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

    Default RE: Form drop-down list puzzle - any suggestions?

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

  4. #4
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default 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

  5. #5
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default RE: Form drop-down list puzzle - any suggestions?

    Ooops

    Sorry forgot attachment.

    RegardsKeith HubertAlpha Guild MemberLondon.KHDB Management Systems

  6. #6
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default 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

  7. #7
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default RE: Form drop-down list puzzle - any suggestions?

    Bret, apologies for sending you up the wrong tree.

    Tim

  8. #8
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

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

  9. #9
    Member
    Real Name
    brett sinclair
    Join Date
    Aug 2002
    Location
    Wellington, New Zealand
    Posts
    858

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

  10. #10
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default 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

Similar Threads

  1. On the Fly Drop Down List
    By Rhett Scott in forum Alpha Five Version 5
    Replies: 2
    Last Post: 01-22-2005, 10:13 AM
  2. Companion form/browse not on drop-down list
    By Roberta Stack in forum Alpha Five Version 5
    Replies: 1
    Last Post: 07-09-2004, 05:23 PM
  3. Drop Down List Object vs Field Rule Drop Down
    By forskare in forum Alpha Five Version 5
    Replies: 8
    Last Post: 06-08-2003, 05:33 PM
  4. Drop down list box
    By drgarytraub in forum Alpha Five Version 5
    Replies: 19
    Last Post: 08-06-2002, 04:58 AM
  5. Drop down list to Choose New Form
    By Phil Rolf in forum Alpha Five Version 4
    Replies: 4
    Last Post: 01-10-2002, 08:42 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •