6 Replies Latest reply on Apr 6, 2011 2:16 AM by JessOttman

    UNIQUE NUMBER PER NO OF RECORDS??

    JessOttman

      Title

      UNIQUE NUMBER PER NO OF RECORDS??

      Post

      Hi

      I have a DB which is for certificate of conformances. The  old database was too slow to enter all the data and then if mistakes  were made it wasnt a case of change one field they're all changed.  Aswell they're were three files involed hence the slowness and lack of  quick correcting.

      So i've combined these three files and out them in one DB.(Re-created though)I have a standard form ''products'' 1 page per record etc. When you click ''certs'' it goes to list view with all the products from the standard form being obtained via a script that pulls the records by a PO#.

      At  the header i have a certificate number which i need to apply to fifteen  records and then change. I have the page break at fifteen. So i need 1  unique number per 15 records and then to change for the next 15?

      Not sure this can be done in a list view layout but and other suggestions greatly appreciated!!!!!!!!!

      Thanks

        • 1. Re: UNIQUE NUMBER PER NO OF RECORDS??
          philmodjunk

          Is this number fixed or should this number change automatically if you perform a find or sort the records differently?

          assuming the number should change with changes to the found set, you could use this calculation in an unstored calculation field:

          Ceiling ( Get ( RecordNumber ) / 15 )

          That numbers the first 15 with 1, the second 15 with 2 and so forth.

          If you need the number to be kept unchanged, you can define a number field and use the above calculation with Replace Field Contents to assign the number to this number field.

          • 2. Re: UNIQUE NUMBER PER NO OF RECORDS??
            JessOttman

            Hi phil thank you for your answer. It was a great help. Just wondering if was to put this cert field on the standard form could i specify it to change with the po#? EX: if i have 2 orders with 10 products on each with the same po# but different system number can the cert field change with the po#? po#xlk= cert number 255 and so on po#jkh=269

            also can the number be unique,and not a page number grouping the records 15=1 + 15=2. I know you can probably set it auto enter serial but i need it the same for the po#

            sorry for the confusing question

            thanks!

            • 3. Re: UNIQUE NUMBER PER NO OF RECORDS??
              philmodjunk

              It can be done, but do not use this method to produce a primary key for relationships. In a shared environment where two or more users may be generating records in this table at the same time, it is difficult to ensure that you don't get duplicate values--unlike using a straight serial number. If you must have this numbering scheme, use it as a field for "search, sort and find" purposes, but don't use it in a relationship.

              What you are requesting now appears to be very different from your original post. I need to make sure that I fully understand the numbering scheme you want to implement here. Keep in mind that I don't know the full structure of your system nor do I know why you want such a numbering method.

              I think you want two different numbering systems here. One as a kind of "page break" control field and the other to sequentially number records that have a common PO with the sequence restarting at 1 for each new PO#. Is that correct?

              "could i specify it to change with the po#?"

              You do not need to add the field to the layout. (FileMaker does not have forms). If you write the script to sort the records by PO# first, you can then loop through the records assigning the page break control number to each in turn and you can increment the number each time you reach 15 OR when the PO# changes. (You can refer to the PO# in a related table, if it is not part of the current layout's table.)

              "also can the number be unique"
              I think you are asking for a different number from the first request here. That works like this. If you have 20 records for PO#234, They should be numbered sequentially from 1 to 20. If you have 5 records for PO#235, they should be numbered from 1 to 5. Is that what you had in mind?

              • 4. Re: UNIQUE NUMBER PER NO OF RECORDS??
                JessOttman

                Apologies for the confusing posts it is quite hard to explain as it is for my employer and it has changede slightly to the original post.

                I have a standard layout which will be used by multiple users. It holds the info about the product eg id number, size description. I intend this layout to be used for searching entering and editing data.On my other layouts (list view) i have them to only fill data from the product layout.

                So all the work goes into the product layout. No entering or searching, just for them to hit the print button on the certs.Perfect! but...

                Lets say i have 10 products for company A, when i get the PO# XLK. i enter my own serial number eg 040311.In 2 days time i get another order from the same company with the same PO# XLK. but as i put in the po# the cert number 040311 i entered 2 days ago comes up automatically.

                The reason for this particular numbering scheme is that these layouts are already on another file that was created many years ago using repeating fields. So there is endless entering of data just to print out info already entered and stored in another file! On these layouts there is a random number numbering each cert.(15 records)

                But since im changing them and using list view , one number won't work on the other records as when you click on any record the cert number will change.It would be too confusing and easy to make a mix up. Where as if there was a cert number to link to every po# it would stay the same from the first time its entered and come up automatically from then on.

                I hope this makes some more sense and thank you for answering my question

                Jess

                • 5. Re: UNIQUE NUMBER PER NO OF RECORDS??
                  philmodjunk

                  You lost me on that post.

                   but as i put in the po# the cert number 040311 i entered 2 days ago comes up automatically

                  Why does it "come up automatically?" How does this happen? Looked up value? portal? Other value?

                  What table structure and relationships do you have here? I think you have at least two, but would like a clearer picture here.

                  • 6. Re: UNIQUE NUMBER PER NO OF RECORDS??
                    JessOttman

                    Hi phil

                    i found a way to do what i wanted i created a relationship between the cert fields and po. The cert numbers have to be entered once and from then on the po number will pick up the cert numbers that were entered at the start

                    sorry for the bad explanations but thanks anyway

                    Jess