9 Replies Latest reply on Aug 11, 2010 11:11 AM by philmodjunk

    Squential numbering help!

    JoeVehling

      Title

      Squential numbering help!

      Post

      I need FM 10 Advanced to generate account names for me in the format (John Smith > jsmith). Now obviously using this formula there will be duplicates, so I need a formula that will figure out how many other jsmith's there are in the database, and then assigns a username on import (ex. jsmith3) based on that number. I've tried to do it using a summary field, but that doesn't work on auto-enter (because obviously data isn't sorted during an import). I've tried the supposed countif equivalent, but unless I am doing something wrong it seems to base the count only on the record at hand. Any other ideas?

        • 1. Re: Squential numbering help!
          philmodjunk

          You need to identify an AccountID field, as type number, auto-enter a serial number. This will automatically assign a unique identifying number to each new record no matter what the name is.

          That's the basic start to getting a good Primary key.

          There may be other factors here:

          Do you have one table or several? If more than one, how many do you have and how are they related in Manage | Database | Relationships?

          You mention importing data. What data are you importing and ( if you have more than one table ), into what table are you importing?

          If you import data with two records that have the name John Smith, how do you tell whether this is the same John Smith or two different John Smiths?

          • 2. Re: Squential numbering help!
            JoeVehling

            I have a primary key for records in the database, I'm not using it for that purpose.

            I have several tables, but they aren't related to what I'm trying to do.

            The data I am importing it into the table I want the field generated in.

            I have a primary key based on other data that allows me to differentiate the John Smiths.

            I used another field to generate the account names, let's call it ACNPrimary. This gives me jsmith.

            Then we have another field ACN# to count the number of jsmiths in the table. I tried to do this using a summary field, but it won't work on unsorted data, which means I can't do it on import.

            Then we have ACNFinal, which would combine the two to jsmith3.

            • 3. Re: Squential numbering help!
              philmodjunk

              What I can't get my head around is why you need/want to do this when you already have a primary key that doesn't depend on the name at all. What problem does this solve for you? (Knowing that answer enables me to make an outside the box suggestion if it is appropriate.)

              You can certainly create a self join in your relationship graph that links your records by the name field(s). Then Count (selfJoinTable::primaryKey) will tell you how many records have exactly the same name.

              My own preference would be to simply combine name and primary key: NameField & AccountID to create a unique identifier that includes the name--but you may have very good reasons why this wouldn't work for you.

              • 4. Re: Squential numbering help!
                JoeVehling

                The AccountID is for something completely unrelated to the FM database. In short we maintain records in filemaker. We need to export some of these to a microsoft ADLAN environment. One of the fields that needs to be populated is a shorted account name. Also emails would be created with this data as well, so the naming convention we would like is john.smith1 and john.smith2 if there are duplicates.

                • 5. Re: Squential numbering help!
                  philmodjunk

                  Hmmm, I'd have a "duplicate count" number field(called DupCount for this example).

                  Immediately after the record is imported or created a script could check to see if DupCount is empty but Count(DupCheckTO::PrimaryKey) is greater than one. If so, set DupCount to Max ( DupCheckTO::DupCount ) + 1.

                  You'd have to make sure to different people can't run this script at the same time as you might not get unique values when you do so. (If this is something that happens only after importing, you shouldn't have a problem if you include this as part of the post import processing.)

                  • 6. Re: Squential numbering help!
                    JoeVehling

                    I'm a little lost as to how count will help me here since I need it to look over the entire table, not just the record or repeating fields in a record..

                    Example: My table has three records.

                    Name                                           ACNPrimary           DupCount(ideally)         

                    Mary Smith                                  msmith                   1

                    John Smith                                   jsmith                    2

                    Jason Smith                                 jsmith                     2

                    • 7. Re: Squential numbering help!
                      philmodjunk

                      Let's say your table is called accounts.

                      Create this relationship:

                      Accounts::Name = DupCheck::Name (DupCheck is a 2nd table occurrence of Accounts)

                      To create DupCheck in the relationship graph, drag from Accounts::Name to outside the box and back to this same field. A dialog for the new table occurrence box pops up and you can name the new table occurrence "DupCheck".

                      Now you can match a given record in accounts to all other records with exactly the same text in the Name field--including itself so more than one matching record tells you the record has duplicates.

                      Max and Count functions can then be used to compute values based solely on this subset of matched by name records.

                      DupCount serves both as the field to append to the name to get your identifier and as a way to tell that this new record has matching records and needs to be assigned a value.

                      For a record where DupCount is empty but Count ( DupCheck::AccountID ) is greater than one, Max ( DupCount ) + 1 can be used in a script to assign it the next number in the series.

                       

                      • 8. Re: Squential numbering help!
                        JoeVehling

                        Great, thank you! (Never explored table occurences before)

                        • 9. Re: Squential numbering help!
                          philmodjunk

                          If that's a new concept, perhaps this thread will help you understand things a bit better: 

                          Tutorial: What are Table Occurrences?