3 Replies Latest reply on Jun 9, 2009 11:14 AM by philmodjunk

    Relationship Question



      Relationship Question


      Hi All,


      I have started a DB with two forms. One has CustNum the other has TxNum.


      I need TxNum to be in the form CustNum-SerialNum that is the transaction number auto generated and added on to the customer number with a hyphen inbetween (say: 1234-009 that is customer No 1234's ninth transaction)


      I want it so that when a record is commited, whatever the CustNum is, new data is entered into the TxNum.


      How do I even make a start setting this up. I cant even get CustNum to write into TxNum without modification even though I used the relationship graph?


      Any pointers / step tutorial links appreciated !!!

        • 1. Re: Relationship Question

          Check out this thread:  Assign a series number



          You'll have to modify the trick a bit, but it should work.

          • 2. Re: Relationship Question

            Actually I dont think I explained this right. And, Im not sure if a relastionship or a lookup is gonna help me.


            What I want to do is this :-


            When a new CustNum is automtatically created in one table then a new record with TxNum is created in another.


            I woud also like several other fields to be copied into the 2nd table.


            Essentialy I would have a 'Data Entry' layout. Every time a new customer comes in a new CustNum is created.
            New data about the transaction is also entered and when 'Commit' button is pressed those certain fields

            starting with TxNum create a new record in the 2nd table. If same customer comes in a lookup on post

            code is done and then the new date and details create a new record in 2nd table. So I am keeping a record

            of customers names address in one, and almost auto creating theother based on whats entered into the

            'Data Entry' layout?


            Perhaps I need two databases not two tables?

            • 3. Re: Relationship Question

              In Filemaker, seperate database files and tables within the same file are functionally identical when it comes to linking your data. So setting up more than one database file won't help you.


              I don't see why the technique I referred you to won't work for you, though you will need a script to distinguish between new and repeat customers.


              "If same customer comes in a lookup on post code is done and then the new date and details create a new record in 2nd table."

              Unless you have an extremely small client list, you'll need to do this search first in order to identify repeat customers. Assuming that more than one customer might have the same post code, I'd set up a search form where you enter the customer's post code into global fields, then use a script to search for matching records. If there are no matches, you either have a new customer or an incorrect "post code". Your system can now create a new customer record. If there are multiple matches, you can display a list of matching records for you to choose from. If there is just one match, you've identified a repeat customer and you can create your new entry in the transaction table.


              In Filemaker, you can display your transacation records in a portal on a layout the specifies your customer table. Simply entering data in this portal, either by hand or script can trigger the lookups needed to create your two part id number and  the method described in the thread I recommended will create the values you need.