4 Replies Latest reply on Jun 1, 2010 1:58 PM by jwis12

    Generating dealer number via calculation field



      Generating dealer number via calculation field



      I'm a relatively new user of FIlemaker Pro 11 on my mac running OS X.


          I'm trying to calculate a dealer number based off of 2 tables and a serial number generated on the creation of a new record. The other 2 parts are generated by seperate related tables with the Region_Name and  Store_type ( i.e boutique, speciality shop, etc.) and the codes associated to these items. I have the region/ type selectable on a drop down menu from the application layout ( where i would like the dealer number to be generated) and i can't seem to find a way to hv the calculation field associate the selected values with their code counterpoints and therefore generate the dealer number as the data is being commited.


      Thanks for any help in advance.



        • 1. Re: Generating dealer number via calculation field

          This should work.


          A simple calculation such as SerialNoField & "-" & RelatedTable::StoreTypeCode & "-" & RelatedTable::RegionCode


          Should do the trick.


          What have you tried exactly and how did it fail?

          • 2. Re: Generating dealer number via calculation field

            Well the store region and type are selected from a drop down menu with the options being pulled in from the related dev_Region and dev_Channel (the store type) tables. The codes i'm trying to use to genrate the serial number are part of the record on these tables. For example on the dev_Region table i have 2 fields one for the region name ( ex. Northern California, Southern Califronia) and the code associated with that region (ex. 01, 02 respectively for N Cali / S Cali) and i want the dealer number generated on the Shops table ( which i hv related to these 2 tables). Is there some formula im missing to select the code value if the Shop table has the value selected from the drop down menu to go back to te related table and somehow pull the associated code # with it?

            • 3. Re: Generating dealer number via calculation field

              I don't see where the difficulty is here.


              Let's check my understanding here.


              Three tables: dev_Region, dev_Channel, Shop


              Somehow the three tables are related (you don't say how).


              You have two drop down fields in Shop, one displays values from dev_Region and one displays values from dev_Channel.

              I recommend that you set these fields up as two column value lists with column 1, the code and column 2 the name.

              Let's call these two value list formatted fields: Region and Type.

              I'd link my tables in relationships this way:


              Shop::Region = dev_Region::code

              Shop::Type = dev_Channel::code


              If the value lists are short, I'd make them pop-up menus and hide column 1. If they are longer, I'd keep them as drop downs, but add the fields for column 2 from the dev_Region and dev_Channel tables to the layout, placing them next to the drop down lists.


              You have another field set up as a serial number field in shop. We'll call it shopID--and I'd use this field to link to other tables and use the field we are discussing for report and label purposes only.


              The calculation for DealerNumber might be:

              Region & Type & ShopID

              • 4. Re: Generating dealer number via calculation field

                That cleared it all up! Thank you for bearing with my inexperince and helping me solve this problem