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

    Generating dealer number via calculation field

    jwis12

      Title

      Generating dealer number via calculation field

      Post

      Hello,

      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.

       

      John

        • 1. Re: Generating dealer number via calculation field
          philmodjunk

          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
            jwis12

            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
              philmodjunk

              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
                jwis12

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