4 Replies Latest reply on Oct 9, 2011 9:27 AM by KirkSchanze

    splitting data into two fields

    KirkSchanze

      Title

      splitting data into two fields

      Post

      Hi all, I have an Excel spread sheet with three columns of data that I need to split into two fields in the DB.

      The information is in three parts on the spread sheet.

      1. Municipality
      2. Fee Description
      3. Fee Rate

      I need to have a drop down or pop-up box for one field that lists the Municipality and the Fee description.

      The second field  I need to list the fee rate.

      The catch is that I want the fee rate to populate the field when someone selects the Municipality. I’m also wanting to be able to change the fee rates when there is an increase and somehow be able to import it in to File Maker pro.

       

      I’m not sure if I need to set up a value lists or how to relate or link these fileds in the DB.

       

      Any help or guidance would be much appreciated.

      Bob

        • 1. Re: splitting data into two fields
          Sorbsbuster

          For example, you could:

          - combine the first two fields in Excel, before importing.  For example, as A1&" - "&B1
          - import the new combined field and the Fee Rate into its own table in FM
          - you could use a value list showing the two fields - or rather use the Fee Rate as the first field, the MunicipalityAndDescription as the second field, and 'Only show values from second field'.
          - attach that value list to the Fee Rate in the field where you want the data held, and they will see the Municipality in the pop-up but it will enter the Fee Rate in the field
          - Another way to achieve the same thing is to have two fields in your end table, chose the Municipality from a pop-up of its values and have the Fee Rate come across by lookup.  Both have the same effect (but your destination table would then have access to both values, which may be useful).
          - You can reimport the updated fees any time, either by deleting and reimportimg, or updating the existing values.  Although why not just do the update to the fees directly in the FM file?

           

          • 2. Re: splitting data into two fields
            KirkSchanze

            Thanks Sorbsuster! 

             

            I've gone in and imported both fields from Excel and created another table called Municipality and have worked up a value list for the pop-up. That part works perfectly in my new table.

            I'm not able to get the fee rate to lookup on my table. I'm using a rate field on my main table and even after setting up a relationship between the two fee fields in both tables nothing comes up in the price field?

             

            I'm sure I'm not doing something just right there. I've read the help on ghow to do lookups but I must not be getting the concept. I'm almost there. I just need to figure out how to populate the free rate field once I select the municipality from the pop-up menu I've created.

             

            Any suggestions?

             

            Thanks a bunch,

             

            Bob

            • 3. Re: splitting data into two fields
              Sorbsbuster

              If you went with the Lookup option, then you should have:

              - in the main table a field called MunicipalityAndFeeDescription.  Presumably this is a pop-up list, simply derived from the new (concatonated) field of MunicipalityAndFeeDescription you imported from Excel.
              - a relationship between the main table and this Fees table, linked by MunicipalityAndFeeDescription = MunicipalityAndFeeDescription

              Then the Fee Rate field is set as:

              - It is set as a number field, with the auto-entry option set to 'by lookup'.  It should 'lookup' using that MunicipalityAndFeeDescription = MunicipalityAndFeeDescription relationship, and should return to the FeeRate field the Fee Rate in that other table.

              Don't try to combine both options - either use the lookup option with one field as the MunicipalityAndFeeDescription and the second field as the Fee Rate, or use a two-field value list and only have a field for FeeRate.

              • 4. Re: splitting data into two fields
                KirkSchanze

                Hi again, I've got things wqorking perfectly now.

                 

                Thanks soo much for all your help!

                 

                Bob