6 Replies Latest reply on Feb 15, 2013 9:40 AM by philmodjunk

    Table relationship record creation

    ToddBeach

      Title

      Table relationship record creation

      Post

           Hi, I have two databases set up with some common information. One is prepress order creation (e-ticket) the other tracks press run information (Run Tracker). I have a relationship set up where Run Tracker can pull info from e-ticket via a portal. The field that triggers it is called L#. I have it set to Allow Creation of Records on the e-ticket side of the relationship. That all works fine by entering the L#, but whenever I create a new record in Runtracker that has not been entered in e-ticket it creates a new record there. If I turn off Allow Creation of Records or move it to the Run Tracker side of the relationship the fields in the portal disappear. My question is how can I allow Run Tracker to pull info from e-ticket and also create stand alone records as existing items do not need to have a e-ticket record created?

        • 1. Re: Table relationship record creation
          philmodjunk

               What you describe does not make sense. Allow creation should not create a new record in the portal if you create a new record in the layout's table. A new record in the portal's table should only happen if you enter data in the "add" row that appears below the last row of data of the portal.

               If you go to Manage | Database | Tables, do you see one table defined or two?

               WHen you then click the Relationships tab to see the relationships graph and hover the mouse over the arrow in the upper left corner of e-ticket and Run Tracker, do you see the same text appear (the name of the data source table) or different text?

               Also check the fields in the portal by clicking them while in layout mode and noting the text that appears in "Display Data From" in the Inspector. The text to the left of the :: should be exactly the same as the text that appears in "Show Related Records From" in Portal Setup...

                

          • 2. Re: Table relationship record creation
            ToddBeach

                 I appreciate your help with this. Sorry if I'm not explaining it correctly, I'm prety new at Filemaker. Basically I have two seperate databases. e-ticket is for the prepress department and Run Tracker for the press room. The data for new projects that have to go through the prepress dept originates in e-ticket and is assigned an L# (job specific identifer) then gets picked up in Run Tracker when it goes to press. The portal is in Run Tracker and the L# field triggers the rest of the information to be transferred to a new blank record. All good so far.

                 Most prepress orders will become press orders but we have several repeat press orders that do not require any prepress. For those I would like to be able to enter the information in Run Tracker without a matching record being created in e-ticket. Thinking about it I'm wondering if that is even possible since by default the portal needs e-ticket information.

                 In the Run Tracker / Tables display there is only one defined table for Run Tracker. The e-ticket database has only one for e-ticket. In hindsight I probably could have one database to cover everything but like I said, I'm new at this.

                 The relationship is set up in Run Tracker. I've included  a pic for reference. The portal fields appear to be correct.

                 If this isn't something is possible I can create a "Press Only" setting and write a script that will omit the press records from my e-ticket listings.

                 Thanks Again
                 Todd

                  

            • 3. Re: Table relationship record creation
              philmodjunk

                   I am understanding the following key details:

                   You have a layout that shows "Prepresseticket" in Layout Setup | Show Records from.

                   A portal on that layout refers to RunTracker.

                   If so, then you cannot use the portal to RunTracker to create records that do not have a matching record in PrePressTicket.

                   But if you create a layout in this file that is based on RunTracker, you can create such records there.

                   Keep in mind though, that such records will not be visible from the Prepresseticket layout.

                   PS: I see a lot of fields that appear to have the same name except for a number. That suggests that you have a single table where it may be much more efficient an flexible a design to have two tables linked in a relationship with multiple related tablesrecords taking the place of those many numbered fields.

                   Also, if L# is a value that you are manually entering in a field, it would be much safer not to use it as the match field in your relationship. It would be better to use an auto-entered serial number for that purpose.

                   Finally, If you are copying data from prePresseticket into RunTracker or the opposite, this usually is not necessary unless you want to modify the copied data without changing the original. Instead, you would keep this data in one table without copying it into the other, you'd just add the fields from the related table to your layout whenever you need to see it in the context of the other table.

              • 4. Re: Table relationship record creation
                ToddBeach

                     I think we are on the same page. So I could basically create a matching layout in Run Tracker without a portal to e-ticket for entering press only records. Could I then have both of these layouts generate entries in a common job list for searching.

                     I'm not sure what you mean in your first PS. Each of the numbered fields represent plate impressions for each color on a single press run. They are totaled over multiple runs for tracking overall plate impressions. I was trying to think of an easier way when building it but this was a solution that worked. The image shows the layout.

                     I do have an auto generated serial number assigned to each record that allows me to run reports and create charts. The L# is our internal nomenclature for order tracking. It has been used for many years and trying to change would be difficult at best.

                     The two databases are being used by seperate departments so the data that I am copying from one to the other eliminates redundancy of data entry, saving time and reducing errors. 

                • 5. Re: Table relationship record creation
                  ToddBeach

                       By the way, the information that is filled in is the portal.

                       Thanks

                  • 6. Re: Table relationship record creation
                    philmodjunk
                         

                              Could I then have both of these layouts generate entries in a common job list for searching.

                         Possibly. You know your business and I don't, but how would that table differ from the Runtracking table that you already have? Isn't it already set up to serve that purpose?

                         

                              I'm not sure what you mean in your first PS.

                         And a significant typo on my part made it harder to understand. I've now corrected that mistake. The idea I am suggesting is that you set up a table where you have one record for each plate impression instead of one field or set of fields for each. A portal to such a table can be  used to record the data for each plate impression. Your totals can still be computed from this related table.

                         

                              The L# is our internal nomenclature for order tracking. It has been used for many years and trying to change would be difficult at best.

                         By all means, continue to enter this value in your database records, but I strongly recommend that you not use it as the match field in your relaitonships. The main issue is that if that value is entered incorrectly and then related records that link by it are added, correcting that error has to be done very carefully so as not to break the link between related records. This issue is fully avoided by using a serial number field as your match field.

                         This is a change that is not really that difficult to make in your existing records.

                         Add an auto-entered serial number field to RunTracker. Put it on your Run Tracker layout, show all records and use Replace Field Contents to assign a serial number to all your existing records. Put a number field in the eTicket table. Use Replace Field Contents with the calculation option to copy the serial number value over from the related Run Tracker table. Now modify your relationships to match by the new fields instead of L#.

                         

                              The two databases are being used by seperate departments so the data that I am copying from one to the other eliminates redundancy of data entry, saving time and reducing errors.

                         Sorry, but I have to disagree with that statement. Putting the same data in two different tables increases the chances of data errors as any changes to this data has to be made identically in both tables. And you do not have to copy the data over to make it visible on layouts based on either table. Once the data is entered in RunTracker, you can put these fields from RunTracker on your eTicket layouts to show that data (and make it accessible for data edits if that is needed) without needing to copy the data from one table to another.