I have a form called Stores_info. Stores contains a field called Store_ID that is unique for each store. The Stores form contains a subform called Zip_Codes_Served, and the subform consists of one field called Served_Zip_Code.
For example, Store_ID #1 serves zip codes 10001, 10002, and 10003. Thus three rows in the Zip_Codes_Served subform for Store_ID #1, with the one of the rows containing Served_Zip_Code = 10001, another containing Served_Zip_Code = 10002, and the third containing Served_Zip_Code = 10003
I now want to use the Served_Zip_Code data in a script in another form called Routes. When a store is selected for a route, I use the On User Input to retrieve the Served_Zip_Code data for that store. I can retrieve the data okay, but I can't find a way to sort it before working with it (and unfortunately users have not always sorted the zip codes before entering them in the Stores_info table).
I tried the following, but I get an error message that it found a "." in the for each
storeRec = Store_info [Store_ID = input.Store_ID];
for each itr in storeRec.Zip_Codes_Served sort by storeRec.Zip_Codes_Served.Served_Zip_Code
{
<logic here to work with the data>
}
When I try this, the Save Script drops the whole sort by phrase and I get the Served_Zip_Code data, but I get the data in the order entered by the users and not sorted by Served_Zip_Code
storeRec = Store_info [Store_ID = input.Store_ID];
for each itr in storeRec.Zip_Codes_Served sort by Served_Zip_Code
{
<logic here to work with the data>
}
Is there a way to retrieve the subform data and sort it when I work with it? I know I can get it sorted in a view, but I need to be able to work with it.
Thanks
JP