4 Replies Latest reply on Oct 6, 2012 2:15 PM by JasonO'Berry

    sequencing serial number?



      sequencing serial number?


           Im designing my first real database and i need some guidance. My client enters prints into a prints table (name, size, cost,max number,etc). Each print can be printed up to what the maximum is set to throught the print_sequence table.A sequence is created by referencing the print_id in the prints table and should return 1 of 50, 2 of 50 and so on. This sequencing is whats throwing me. In the print_sequence table how do i define the correct sequence number. I tried using a summary field and also a getSummary function to give me the count of the print_id records but that updates on every record and doesnt store that individual sequence numbers print sequence. Sorry if this is confusing. So whats the best route on this one? Perhaps a new table occurance with a new relationship or something. Thanks for reading!

        • 1. Re: sequencing serial number?

               This is confusing.

               You need to start by describing each table involved in this issue and how/if they are related to each other.

               You may find it easiest to do a screen capture of manage | Database | Relationships and then use the controls below "Post A Answer" to upload the screen shot. If you do, please briefly describe the purpose of each table occurrence in the screen shot that is part of this issue.

          • 2. Re: sequencing serial number?

                 sorry for the delay, but thank you for the response. Ill try to clarify. My client is a photographer and he sells each print he creates in editions of 50. So he creates his print (in the prints table) and defines its name, size, suggested price and the number of times that print can be sold (usually 50 - each one is a sequence). Then, when he gets an order for that print, he creates a record in the prints_sequence table logs the print name, size and sequence number of that particular print (1 of 50 and so on). I cant figure out how to track the sequence number (of the 50) of each print.

            • 3. Re: sequencing serial number?

                   A side note: It looks like your invoice_details 1 and print_sequence 1 tables could be merged into a single table. It looks that way since I assume that you can only list a given print once on a single invoice. This is not a change you have to make however.

                   Define a calculation field, cMaxSeq, in Prints set to evaluate from the context of Prints 1.

                   Define it as: Max ( print_Sequence 1::sequence_nbr )

                   Put a button on your Prints 1 layout. Set it to run this script:

                   Set Variable [$PrintID ; prints 1::print_id]
                   Freeze Window
                   Go to Layout [print_Sequence 1]
                   New Record/Request
                   Set Field [print_Sequence 1::print_id ; $PrintID]
                   Set Field [print_Sequence 1::sequence_nbr ; prints 1::cMaxSeq + 1]
                   Go to Layout [original layout]

                   If you put a portal to print sequence 1 on your Prints 1 layout, clicking this button will cause a new record to automatically appear in the portal.

                   Please Note that this solution assumes that only one user will ever be creating new sequence records for a given print at a time. If two users tried this at the same time, you might get two sequence numbers for the same print that are the same. There are ways to add a validation rule that catches such instances should that rare but not impossible event occur.

              • 4. Re: sequencing serial number?

                     thank you philmod.