8 Replies Latest reply on Mar 3, 2015 6:12 AM by philmodjunk

    Desperate!! please  .... lost relationship logic when new records added

    regaldridge

      Title

      Desperate!! please  .... lost relationship logic when new records added

      Post

      Hi,

       

      I have a parts stock file with 200k records for four different vehicle manufacturer parts. On Saturday I deleted 75k MG Rover parts in order to cleanse them. I put them back with some new parts some new prices, descriptions etc etc and now my Invoicing screen has lost the connectivity with ALL of these updated records eg When I put an MG Rover part number in the first line of a new invoice  the part description and retail price just won't appear from the related parts master table. All other "non-updated records" work just fine, as before ? I am completely lost .... this application has worked faultlessly for eight years, passing through several versions of FMPro. I feel sure that I must have "upset" the table relationships somehow but I cannot see how .... especially as ALL other non updated parts are unaffected and still maintain the correct table relationship. Any ideas will be most welcomed. Enthusiastic but bewildered amateur FmPro'er of Endon, Stoke-on-Trent 01782 505050 or reg@endonservices.co.uk

       

      TIA!!!

        • 1. Re: Desperate!! please  .... lost relationship logic when new records added
          Markus Schneider

          Do You have a backup?

          If possible, go back to that. If not possible to go back but You have a backup: Check that backup to find out how relationships were made

          Is the developer still available?

          • 2. Re: Desperate!! please  .... lost relationship logic when new records added
            philmodjunk

            Best guess is that when you "put back" this data you imported this data and either a Primary Key field is now empty or now has a new value different from that of the same part in the original set of records that you deleted. As Markus has suggested, a back up copy of your file may be very helpful in restoring your database back to full functioning form.

            • 3. Re: Desperate!! please  .... lost relationship logic when new records added
              regaldridge

              Hi Both,

              The new records have unique entries in the primary matching key field and these entries are the same values as the equivalent ones in the previous records. I can see field values manually OK on test layouts within that table structure that I have specifically built for that visibility/test purpose. 

              I can't see why these new records are not being related to the fields in the invoice table. I am simply trying to bring in eg "retail price and description from the related table .... has worked fine for eight years with thousands of invoices produced? AND the unupdated records are still working fine ....?

              • 4. Re: Desperate!! please  .... lost relationship logic when new records added
                Markus Schneider

                It's somehow like the 'chaos theory': One small butterfly can affect something big at the other end of a world (-:

                If You find that bit, the solution should be clear -  and therefore a backup would be necessary. Just check the relationships. You can do that in the current version as well (but You won't see the corresponding data)

                • 5. Re: Desperate!! please  .... lost relationship logic when new records added
                  regaldridge

                  Hi Markus,

                   

                  Many thanks for your input

                   

                  I DO have a backup ......but the volume of the data changes (lots of NEW parts records in addition to data changes to existing individual parts record fields, dictated my delete and renew route ..... I have checked the relationship graph in both files and they are identical. I haven't changed anything in the infrastructure ... just deleted records and imported records with same basic data structure. 

                  Let me give an example.....

                   

                  My parts records are keyed on EndonPartNo ( a unique key) and have normal non key fields like .... RRP, Description, Disc Code etc etc 

                  When I now create  a new invoice the previous simple page functioning now fails ie place RRP on the page from related record in parts file via EndonPartNo. NOTHING happens at all. I can quite see that something is "corrupted"?? But I am using only the most basic features of FMPro ..... that I have used since c1982 in one form or another. So I have lots of FMPro experience but few technical skills, it seems!

                  I am using FMpro 11, but have 12 too.

                  I am going to transport the problem DB to FM12 ..... to see if that cures it?

                  I cannot imagine that a simple basic feature like this can not be working, so I must have introduced a fault in the data somehow. Do you know of any way to "reconnect related table keys/data" in situ? Bearing in mind that 120k unupdated records are working perfectly still?

                   

                  TIA!

                   

                  Reg

                   

                   

                   

                   

                  • 6. Re: Desperate!! please  .... lost relationship logic when new records added
                    philmodjunk

                    We are operating in too much of a vacuum here. We just don't know enough about the design of your database and exactly what you did with your data and thus cannot provide much in the way of detailed advice.

                    You'll need to provide more info about your database design first.

                    • 7. Re: Desperate!! please  .... lost relationship logic when new records added
                      regaldridge

                      Hi BOth,

                       

                      I have found the problem ...... and I am suitably embarrassed but feel that the ACTUAL issue might affect other users.

                       

                      The data that I was using to update the records was from a "reliable source" and I used it without questioning it as I had used it MANY times before (over a period of eight years!)

                       

                      However this time the incoming data feed had phantom and invisible trailing spaces on the primary key field ... the part number. This messed up the relationships and was not evident until I tried an alternative "matched key" update instead of my previous "delete and replace" strategy.

                       

                      I hope that this might help other users ..... please don't assume the consistent quality of incoming external data until you have inspected it first!

                      Many thanks to my two contributors!

                      • 8. Re: Desperate!! please  .... lost relationship logic when new records added
                        philmodjunk

                        Even more to the point, don't use externally generated values as your primary key! This leaves you at the mercy of such problems.

                        Use a field defined in FileMaker to generate a serial number or use  Get ( UUID )  in a text field to generate an ID string. Use a field like the imported part number only for temporarily matching records so that you can update foreign key fields to match to the newly imported records via a standard primary key to foreign key relationship.