3 Replies Latest reply on Mar 31, 2011 10:39 AM by philmodjunk

    Serializing Records By Category



      Serializing Records By Category


      Being new to Filemaker (pro 10), I am having trouble Serializing Records by Category.  I have looked at and tried the solutions listed (which all seem to relate to FMP 4 or lower), but can't get them to work.  What I want is simple.  How to achieve it is proving impossible!

      I have a Customer table (containing some 10,000 records) with a CustomerID number field auto generated. I have a Quotes table, linked by CustomerID, which contains multiple finance quotations for each customer.  All I want to do is add an auto generated field in this Quotes table, named Quote_No., that sequentially names each quote for each customer, starting from 1.  Can someone tell me how to do it please?

        • 1. Re: Serializing Records By Category

          This is a question better answered in the FM Pro forum section. That's OK, we can answer it here, but you might have gotten your answer earlier had you posted it there.

          Such a serial number sequence that restarts with each customer is not a built in feature of FileMaker. It can be done, but if you have more than one user creating quotes records at the same time for the same customer, it's possible to get two quotes records with the same serial number so this must be implemented with caution and the resulting number should not be used as a Primary key to link the record to other tables.

          Are you sure that this is something you want to do? What problem is solved for you by having this type of numbering sequence? (Maybe there's an alternative that avoids generating such a number series.)

          If you still want to do this...

          With this relationship:  Customer::CustomerID = Quotes::CustomerID

          You can define a calculation field in Customer, cMaxQuoteNumber as Max ( Quotes::QuoteNumber )

          In Quotes, define a field of type number with this auto-enter calculation: Customer::cMaxQuoteNumber + 1

          Be sure to clear the "Do not replace existing value..." check box or it won't update correctly when you add a new Quote. The Quote Number should compute and display once you enter a CustomerID number in Quotes::CustomerID--this can be automatic if you are entering data into a portal to Quotes on a Customer layout.


          • 2. Re: Serializing Records By Category

            Thanks Phil. Having just signed up I had'nt realised that there were two separate forums!

            I am already using the relationship Customer::CustomerID = Quotes:CustomerID. In the Quotes table, I have a field, QuoteID, as an auto serial number.  From the Customer layout, I have a button to switch to the Quotes layout that shows the latest Quote for that customer. The Quote layout also has a portal to be able to switch to other quotes, should they exist.

            What I was trying to do was get the portal to display the alternative quotes for any particular customer that were numbered 1 to 6(say) in decending creation order. However, I gave up on that.  I now show all (rather than alternative) quotes in the portal listed in ascending order (by QuoteID) using @@ in the portal to number them.  This seems to work and be less confusing for the user.

            However, I am a bit worried by your comment about multiple users.  It is quite possible for 2 or even 3 users to be working on the same record. If I were to simply add your suggested calculation fields to my existing tables, retaining my QuoteID field, would this not work? I should add that once created a quote is non-editable and non-erasable by any user.  If they want to change anything at all, they have to create a new quote.

            • 3. Re: Serializing Records By Category

              If you just need the current record number, what you get with the @@ layout symbol, please ignore my entire post. It won't be an issue for multiple users. Just be aware that this number will change just by deleting records or changing the order in which they are displayed.

              The multi-user issue would only be a factor if you needed the numbers physically stored with each record like I originally assumed you wanted. It then would only happen if two users were creating new related records for the same parent record. In your case that would be two users trying to add a quote record for the same customer. If they did this at nearly the same instant, you might get two quote records with the name number in the series. It's also possible that one user would have the customer record locked and this might lock the second user from doing this until the first user commits the record. You can set up a validation rule to catch instances where a duplicate value is generated if you use an auto-enter calculation to combine the customer ID and quote ID and then specify unique values on this added field.