1 2 Previous Next 19 Replies Latest reply on Jun 30, 2011 6:53 PM by RonCates

    Make new record in related file?

    AdrianWilliams

      Title

      Make new record in related file?

      Post

      Master file has a unique globalTEXT field 'kp_Customer_Reference_ID'. File I want to relate file has globalTEXT field 'kf_Client_Ref_ID' Each file has a field where I can enter the Client reference like: ASam/34

      When I make a New Record in Master file how do I also make it happen in the Related file?

      Is this not working because I HAVE to use Serial Number IDs for records as in some vids I've watched?

      I have checked the option on both sides of the define relationship dialog (in both files) to Create new records via this relationship.

      Thanks,

      Adrian

        • 1. Re: Make new record in related file?
          RonCates

          Creating records though relationships is done through portals for creating related child records. It sounds like you want to make a copy of a record you are creating in the master file. The fact that you want to make duplicate records is often an indication that the data structure may need rethinking.

          You may want to describe what it is you are wanting to do and why. You may find that the people here can point you in a better direction.

          If you have a valid reason for creating the same record in two places you will probably need a script to do it.

          Also, not using an auto enter serial number for relationships will, with very rare exception, always come back to bite you in the butt. Take the advice of all the masters on this board and others and use FMs serials  IDs for relationships. You can keep your other identifiers for user refferences but don't use them for relationships.

          • 2. Re: Make new record in related file?
            AdrianWilliams

            I wanted to keep all of one tytpe of investment product in its own file and somehow relate that to the 'Contacts Management' file.

            That's good advoce - I've realised I need a rethink and now considering keeping everything in one file to make it simpler to administer. Any pointers would be really helpful. There are 20 investment products that each investment product requires its own set of 7 different letters to Clients and many fields, buttons checkboxes. I started with the 'Contacts Management' Starter Solution and went from there but struggling with Relationships (no change there then)!

            • 3. Re: Make new record in related file?
              RonCates

              Having multiple files is a personal choice although it may make things a little more complicated for you. My point is that you shouldn't need to have the same records in 2 places. The seperate file of products can pull contact information from the contact file or vice versa. That's the point of relational databases, each entity has it's own table. The tables are related to access common information. For instance if you have two products for one client you would have one client record related to two product records. Then each of those product records accesses the information about thier common client through a relationship.

              You will need to provide a much more detailed description in order for us to give you propper advice. But from the information you provided one example of propper structure here would be that you would have one table for products and a seperate related child table for letters. Each product record could have a portal that lists the 7 related letters from the letters table.

              What we need to start is a description of your data like this.

              One client can have only one product or many products
              One product can belong to only one client or many clients
              One product can have many letters
              One letter can belong to only one product or many productsThe above is based on what you have provided so far. Let us know if there is anything else in between. Such as a Client can have many invoices, an invoice can have many products. etc.

              Then we can start to layout the table and relationship structure.

              • 4. Re: Make new record in related file?
                philmodjunk

                "Do I put it all in one table or use separate tables for each" is one of the classic questions a database developer has to answer when designing a new system. Often, the answer is not a simple yes or no but a case where you have to evaluate trade-offs in light of the data and the purposes to which that data will be put when users access the database.

                One compromise is to set up a unified table where each record is a single product, but which then links to the appropriate detail table for product specific fields. This often makes it possible to set up a report of data from all your products based on that single table and using data common to all the products, but still be able to use layouts customized to each product that combine fields from the common table and the product specific detail table.

                When it comes to your "7 different letters" there are different ways to manage them from a related table depending on how those letters need to be produced. If they are "canned" and don't include data from your database, you can store them in a table of container fields as PDF's and each product can link to a different group of these records. If they need to include data from contacts and/or the Product table(s), you can store template text in text fields that include place holder text for each field that will supply data to the final letter. Then it's possible to produce a letter for each contact/product by using the Substitute function to replace the place holder text with actual data from your database. I think at least one of the starter solutions uses this technique, but don't remember off hand which one.

                • 5. Re: Make new record in related file?
                  AdrianWilliams

                  What I struggle with is a simple explanation how the Serial Number allocated in the Master file gets into a related file. I need to trace a Clients investments in that related file. There are toooo many relationships to keep track of them. Even that defeats me, so I think its easier to keep it all under one file.

                  One client can have MANY products

                  One product can belong to MANY clients

                  One product can have MANY letters

                  Seven letters can belong to only ONE product

                  Client can have MANY invoices

                  An invoice can have MANY products.

                  • 6. Re: Make new record in related file?
                    RonCates

                    Here's another way of looking at how a relationship works. A parent record has an auto enter unique serial number. Let's say 10 for example. All the child records related to that record get a 10 populated in the foreign key field. So looking through a relationship from parent to child is like doing a search for all the records in the child table that have a 10 in the foriegn key field. On a layout based on a client record if you have a portal to an invoice table and if it is set to allow creation of records, then whenever you create an invoice record using the portal the first thing that happens when that record is created is the id of the parent record gets automatically entered into the friegn key field. So for a client with an ID of 10, every invoice created for that client will have 10 in the foriegn key field. Filmaker allows you to use a step called GoTo Related Record which is essentially like telling it to go to the invoice table and look for all invoices with a 10 in the fk field.

                    As for your table structure. It sounds like you are creating an invoicing solution which is very common and there are many posts and examples around the boards that you may find helpful. Phil is usually good for having all the links. But the basic table structure of an invoicing solution is as follows:

                    Clients --------< Invoices -------< Line Items >------ Products --(and in your case)---< Letters

                    -----< represents a relationship. < > these represent the many side of a relationship. so

                    One -------< Many
                    or
                    Primary Key ------------< Foriegn Key

                    The Line Items table is what ios called a join table. It is an instance where one product is assigned to one invoice.  Most all relationships should be a one to many relationship. In the case where an incvoice can have many products and a product can be on many invoices that would be a many to many relationship. So we put a join table inbetween that has a many to one relationship on either side. At the bear minimum a join table has two foriegn keys. One holds the ID of the invoice and one to hold the ID of the product.

                    Hope that's helpful

                    • 7. Re: Make new record in related file?
                      philmodjunk

                      There are several basic ways to manage the creation of new related records so that the new child record is linked to the correct parent record.

                      Simplest is to enable "allow creation of records via this relationship" and then use a portal to the child table to create new records. Entering data into the bottom blank row of your portal creates a new child record and copies the parent's ID number into the child's. This is very effective for any case where you have a one to one relationship.

                      If you have a one to one relationship such as you might have with a combined table and a record in a product type specific detail table, you can 1) enable this same option in the relationship, 2) base your layout on the combined table but include fields from the related detail table on the same layout. When you type data for the first time into a field from the related detail table, this option will create the new child record and enter the ID number into the Child's key field for you just like a portal. (The key difference is this only works to create the first related record so it's only useful for one to one relationships or cases where your only need is to create the First related record in a one to many relationship if one does not already exist.)

                      Other methods can use scripts to copy the ID number into a variable, (or pass it in a script parameter), then switch to the child table's layout to create the new record and enter the ID number from the variable or script parameter.

                      Likewise, it is sometimes useful to use OnRecordLoad to load the current parent record's ID into a variable or global field and then the child record's ID field can be set to auto-enter the value currently stored in this variable or field.

                      Other times it works better just to set up the child record's ID number field as a drop down list or pop up menu of parent record ID's for the user to choose when creating a new record.

                      • 8. Re: Make new record in related file?
                        RonCates

                        To further expand on how the related records get the ID of the parent, there are several ways. I described above how a portal enters the ID automatically. Another example might be on an invoice. You might have a drop doen list of clients to assign the invoice to. When you select the correct client the Clint ID is then entered into the FK field assigning that invoice to that client.

                        From the client side you might have a button to create a new invoice for a client. The script attached to that button would basically do the following.

                        Record the Clint ID ( in a variable usually )
                        Goto Invoices
                        Create a new record
                        Put the Clint ID in the FK field

                        Now you have a new invoice related to the client you started from.

                        So in a lot of cases, you as the developer dictate how the Parent ID gets put into the child record.

                        • 9. Re: Make new record in related file?
                          RonCates

                          Hi Phil. I see I'm typing answers at the same time you are. :)

                          • 10. Re: Make new record in related file?
                            AdrianWilliams

                            Your help has made me decide what scheme I'll use. Each Product will have its own separate file. Each file will be related to Contacts Management. So far so good. But the reason I asked about 'related' files was that I've spent 3 days thinking it's my fault it stops working. I get a relationship working to transfer Name & Address from one record in Master file to Related file, but it will not transfer over the info from Master file 2nd record to portal in related file. I fiddled around to no avail and then, even the first record could not been seen in the portal fields.

                            In Table view I get <Table Missing> everywhere. This in turn causes <Unrelated Table>. But the Fields are still there in Manage>Database>Fields. So questions are:

                            What can cause <Table Missing> to happen? I changed field Type from Number to Text a couple times - is that the cuase?

                            How can I re-instate the <Field Missing> <Table Missing> if the fields are all still there?

                            What causes <Unrelated Table> to happen?

                            In short, are there some REALLY, REALLY don't do things in FM that will break relationships???

                            This whole endeavour is doomed unless I can get a handle on why I keep loosing days of work.

                            • 11. Re: Make new record in related file?
                              AdrianWilliams

                              Created New file+relationship again for 11th time today. Apart from the relationship breaking issue, before this file breaks again I must pass on what I discovered about why I'm confused.  Two questions arise:

                              HOW TO GET RECORDS BACK IN SYNC..?

                              Masterfile file First_Name, Last_Name ------------------- Related file First_Name_Last_Name

                              Find Client No: 1 Record 1:S Wils  ---------------------- Client 2: S Wils  (Record 1 was deleted)

                              Find Client No: 2 Record 2: A Berty --------------------- Client 3: A Berty

                              I enter a record number in 'Client' number field to retrieve portal items. At the moment if I delete a record from either file it throws everything out of wack. How do I resolve this. Should both files have "Delete related records..." check on on both Master and related files.

                              HOW TO SEARCH FOR THE CLIENT ID RATHER THAN A RECORD NUMBER..? (because they could get out of sync).

                              But each record has a unique COMMON ID other than the record number., SWils-42  and ABert-79 for example

                              • 12. Re: Make new record in related file?
                                philmodjunk

                                Name based keys are not a good idea. People's names are not unique and people change their names.

                                The records should be linked not by record number but by an auto-entered serial number defined in the parent record. The related record in the child table should not be defined as an auto-entered serial number. It should just be a number field. With this set up, deleting a record from either table does not affect what record matches to the other record.

                                • 13. Re: Make new record in related file?
                                  AdrianWilliams

                                  Decided to offer 2 tiny files (60k each) to illustrate this issue....

                                  http://www.clubtype.co.uk/fmpro11/Contacts.fp7

                                  http://www.clubtype.co.uk/fmpro11/Product_DES.fp7

                                  Product_DES just imports Name_First, Name_Last into portal. As sson as I delete a record it breaks the functionality and nothing gets put into the 'Product_DES' file portal. I understand the principle of the relationship, but not why different record numbers matter. I want to search records y common Client ID (Text field somehow anyway). Thanks ;-{

                                  • 14. Re: Make new record in related file?
                                    philmodjunk

                                    You have a field that should be deleted: Product_DES::PK_Client_Ref

                                    This field is already defined in Clients where it is an auto-entered serial number. It has no purpose in your Product_DES table as your relationship is currently defined as:

                                    Contacts::PK_Client_Ref = Product_DES::Client

                                    This appears to be the source of confusion here as new product records will generate new numbers in this field, but these numbers in this field which have no part of your relationship as it is currently defined. (And an auto-entered serial number field shouldn't be used here for the product side of the relationship anyway.)

                                    I can control what records appear in the portal by entering different numbers in client. Since there is only one record in Contacts with any given PK_Client_Ref number, I will never see more than one record in the portal.

                                    Thus, the relationship works as you have designed it, but the Product_DES::PK_Client_Ref field at the top of your screen is producting spurious numbers that have nothing to do with what client record will appear in the portal. Deleting records, creating new ones has no effect on what contact record appears. The key is in what value you enter in the client field.

                                    You can format the client field with a drop down list or pop up menu of PK_CLient_Ref numbers from the contacts table. This value list can include the client name as "field 2". This enables you to select a client by name, but the value list enters the client's ID number. This is the simplest way to set this up. Other more sophisticated methods can be employed to help this method work better when your value lists of clients becomes very large.

                                    1 2 Previous Next