6 Replies Latest reply on Jan 27, 2009 5:54 PM by stoggers

    serial numbers

    stoggers

      Title

      serial numbers

      Post

      Starting from the beginning I want one of the databases to:

      Generate one database of records

      Which has two automated serial numbers;

      first serial number I am going to call the "account id"

      second serial number I am going to call the "ref id"

      I have three layouts;

      first layout is the "master" this details all the fields and is designed to search all the records in the database.

      From the "master" there are two script buttons taking you to the "add account" & "Update account" layouts 

      The "add account" layout is design to generate a new "account id" and "ref id" from here you edit all the fields to create a new record with a new "account id" & "ref id"

      The "Update account" Layout is design to generate a new "ref id" but keep the "account id" from here you can edit set fields and create a new record with a new "ref id" but keeping the same "account id" 

      This is so I can then search and populate other layouts and databases to show all the "ref id's" associated to the "account id" but keep all the records in the same database.

      If you can help then superb.










        • 1. Re: serial numbers
          ninja
            

          Howdy Stoggers,

          Thanks for the post.

           

          From your description you have one table with three layouts.

           

          In the "Master Layout" layout you'll have a button that runs a script to make a new account:

           

          New Record/Request

          GotoLayout [AddAccount] 

           

          This record will have the fields 'accountID' and 'refID' which you have defined as TEXT FIELDS and autoenter serial numbers starting with ACC0001 and REF0001 both incrementing by 1.  The setting to text fields allows you to use and keep the 'ACC' and 'REF' parts

           

          In the "Master layout" you'll also have a button which updates records with a script:

           

          Setfield[refID;newvalue]

          GotoLayout [Updateaccount]

           

          Think carefully about the new value of RefID, though, so your numbers don't overlap sometime in the future...you may want to use a new value like "Newref0001" and change the text part to avoid your number progressions from running into each other and overlapping the autoentry values.

           

          Again, in order to keep the prefixes straight, you'll need your fields to be set as TEXT rather than NUMBER.

           

          Is this what you're after?

          • 2. Re: serial numbers
            stoggers
              

            Hi Ninja,

             

            The problem is that the acc id is a serial number so every new record increases the serial number, so when I update the Ref id the acc id increases by one, I want to update the ref id but keep the acc id (stop it from increasing) 

             

            I was thinking of creating a second table to generate the acc id and then show this in a feild in the ref tab. This way you get a acc id ----one to many ----- ref id relationship.

             

            This is fine but I can not get the acc id to show in a field on the ref table when creating a new record for the ref id.

             

            So I get one table with a se of acc id records and the second table with acc id and ref id. So for each acc id I have a ref id as well. This would work fine but... 

             

            Either I am doing something really stupid, or I cant show this info cross tables because they are set as auto serial numbers, thinking about it maybe have to set up as a normal serial number? 

            • 3. Re: serial numbers
              ninja
                

              Stoggers,

               

              1. If you just want to update data and you don't have to make a new record...then don't make a new record!  This will leave your accID the same, but change the refID...the two step script above has no "New Record" step in it.  No new record, no change in accID.

               

              2. If you must (or really really want to) make a new record for the changes...:

               

              SetField[tempGlobalID;accID]

              duplicate record (or New record, whichever)

              SetField[accID;tempGlobalID]

              SetField[refID;new ref ID value]

               

              The idea is to trap the accID into a global field BEFORE you make a new record whether the new record is on this table or any other.  Since it's global, you can reference it in the following SetField operation without worrying about matching records or what records are related to which.  Then your new record will have the same old accID.  Pay attention here, though --> you will now have two records with the same "serial number" but different refID's.  Make sure that this is what you want, and make double-extra sure that your refID's don't also match otherwise the Dbase would have trouble telling the two records apart during finds.

               

              The reason your AccID won't show up in the other table that you tried is that your records aren't linked even though your tables are related...you make a new record in a table related by accID, and the new, serialized accID doesn't match the original number, and so the record isn't related to the one you wanted to reference.

               

              Let me know if I made sense or nonsense...

              • 4. Re: serial numbers
                stoggers
                  

                Hi Ninja,

                 

                To be honest I see what your saying but I think were I am going wrong is the whole relationships of the tables.

                 

                table 1 (account)

                kp_acc : auto-generating

                 

                 

                table 2 (reference)

                kp_ref : auto -generating

                kf_acc

                 

                So simple; (table 1) relationship to (table 2). I can generate a new record in (table 1) via layout and then can I not copy that data into (table 2) via the "allow creation of record in relationship table"?

                 

                Also the "kp_acc" field should be able to copy across to (table 2) "kf_acc" field via relationship? Even in creating a new record can I not show last record from (table 1). Or should I be writing a script that? 

                 

                I understand that creating a new record in (table 1), no record in (table 2) so no link for "acc id", but (table 2) should show results of (table 1) records in the "acc id" fields when creating a new record in (table 2), this would seem a pretty useful function to create linked records across tables easily?

                 

                Is this only done using control style and drop down menu? I could then generate a new acc id this way and then drop it in to the layout and save record to table.

                 

                It might seem a bizarre request, but I simply want a account number to generate automatically and a separate ref number to generate separately (so no human error) when creating a new record. Then I want to update that account number (so no change to account no) with a new ref number when updating details for that account or when creating an update record (either by duplicating, setting up in a new table, but just dont want any human input error) My new filemaker brain is struggling to find a simple solution.  

                 

                If you can come up with something other the global field script I would massively appreciate it. 

                 

                Cheers. 

                 

                 

                • 5. Re: serial numbers
                  ninja
                    

                  howdy stoggers,

                   

                  I think we're talking past each other.  Let me interlace the answers between the questions and see if we can connect.

                   


                  stoggers wrote:

                  Hi Ninja,

                   

                  To be honest I see what your saying but I think were I am going wrong is the whole relationships of the tables.

                   

                  table 1 (account)

                  kp_acc : auto-generating  {Good stuff, leave it}

                   

                   

                  table 2 (reference)

                  kp_ref : auto -generating   {Good stuff, leave it}

                  kf_acc                            {Good stuff, leave it, this is your related field to Table 1 (account)}

                   

                  So simple; (table 1) relationship to (table 2) using kp_acc. I can generate a new record in (table 1) via layout and then can I not copy that data into (table 2) via the "allow creation of record in relationship table"?  Creating a record and copying data are two different things.  You want a one to many relationship with one account having many references.  If it makes sense in the specifics of your application, I would put the data that changes from reference to reference in Table 2 (references) and the data that only ever changes account to account (but stays the same through multiple references within an account) in Table 1 (account).

                   

                  Also the "kp_acc" field should be able to copy across to (table 2) "kf_acc" field via relationship? Yes.  But only if you create the new record in Table 2 from the viewpoint of Table 1 and use the relationship to create the record.  Even in creating a new record can I not show last record from (table 1). You would be sitting on this record, it would be hard to miss.  Or should I be writing a script that? You can create a related record via the relationship, or do it outside the relationship via the "globalTempID" method I described.  They accomplish the same thing.

                   

                  I understand that creating a new record in (table 1) {This makes a new account} , no record in (table 2) so no link for "acc id", but (table 2) should show results of (table 1) records in the "acc id" fields when creating a new record in (table 2) {It will do so only after the relationship is formed by inserting the proper data into kf_acc.} this would seem a pretty useful function to create linked records across tables easily?  You can do this two ways that I think are pretty easy.  The first (my personal preference) is what I wrote earlier.  From a layout based on Table 1, capture the kf_acc to a global TempID, go to a layout based on Table 2, create new record, set the linking field 'kfacc' to equal global TempID to link the records.

                   

                  What it looks like you're really after, is to portal the related records from Table 2 into a laout based on Table 1.  Now you'll get the "automatic" stuff you're looking for as far as record creation.  When you enter any data into a blank portal row, it will create a new linked record in Table 2.  The fact that it was created through the portal will automatically set the Table 2 kf-acc field to match the Table 1 kf-acc field you're currently on, and the fact that your data entry made a new record (through the portal) in Table 2 will engage the serialized autoentry of kp-ref.

                   

                  Is this only done using control style and drop down menu? I could then generate a new acc id this way and then drop it in to the layout and save record to table.

                   

                  It might seem a bizarre request, but I simply want a account number to generate automatically and a separate ref number to generate separately (so no human error)   when creating a new record. Any of the ways I've described above avoid the human error part.  Then I want to update that account number (so no change to account no) with a new ref number when updating details for that account or when creating an update record (either by duplicating, setting up in a new table, but just dont want any human input error) My new filemaker brain is struggling to find a simple solution.  The update record you're trying to create should be in Table 2.  Table 1 includes fields holding account-specific info, Table 2 includes fields holding reference-specific info.  The relationship between the tables should be the account ID.

                   

                  If you can come up with something other the global field script I would massively appreciate it.  The portals would work fine as well...what you describe is pretty much what they are designed to do.

                   

                  Cheers. 

                   

                   


                  How'd we do?  Please let me know how it's coming together for you...

                   
                  • 6. Re: serial numbers
                    stoggers
                      

                    Hi Ninja,

                     

                    That is spot on, working like a dream. Got my head round the relationships and everything is now progressing at a rate of knots. 

                     

                    Thanks for the help, I hope this thread is now useful for anyone else scanning the web for similar answer.

                     

                    Good work, keep it up helping us newbies out.