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