1 2 3 Previous Next 32 Replies Latest reply on Aug 4, 2017 12:31 AM by mainframe

    Find non-duplicates of related records


      I have 2 tables


      TABLE1         related to                                                                     TABLE2                                         

      NAME1.a                NAME1                                                                NAME1.a                NAME1              ID1

                                                                                                                  NAME1.a                NAME1              ID2

                                                                                                                  NAME1.a                NAME1              ID3

                                                                                                                  NAME1.a                NAME1              ID4


      NAME1.b                NAME1                                                                NAME1.b                NAME1              ID1

                                                                                                                  NAME1.b                NAME1              ID2

                                                                                                                  NAME1.b                NAME1              ID5

                                                                                                                  NAME1.b                NAME1              ID6

                                                                                                                  NAME1.b                NAME1              ID8

                                                                                                                  NAME1.b                NAME1              ID9


      NAME1.c                NAME1                                                                NAME1.c                NAME1              ID1

                                                                                                                  NAME1.c                NAME1              ID2

                                                                                                                  NAME1.c                NAME1              ID3

                                                                                                                  NAME1.c                NAME1              ID4



      'NAME1.a' field in TABLE1 is related to 'NAME1.a' field in TABLE2 etc.

      The first qualifier e.g. NAME1 is stored in a second field in both tables.

      I want to find out based on NAME1 field (not NAME.a,b,c etc) which related sets are not identical.  In this case that would be 'NAME1.b' would not be identical. All tables have unique values apart from 'NAME1'


      what is the smartest way to obtain the desired result telling that NAME1.b is not equal ?

        • 1. Re: Find non-duplicates of related records

          How is the output intended to be displayed? in a list format contained within a single field ,via portal, report?



          Could you create another Table Occurance and relate the Name1 field to to this new TO then use portal?

          Maybe use the execute(SQL) function using WHERE .... ?

          • 2. Re: Find non-duplicates of related records

            Thanks Brian


            I prefer to have it list format as it is ... an SQL could be an option ... no portals at all

            we are talking of 10000 entries in table1 and average 40000 in table2. 

            • 3. Re: Find non-duplicates of related records

              I'm sorry  I just reread the original question and i think I may have misunderstood what you were asking in the OP. Not to mention the example you provided is messed up on my screen due to formatting differences.


              Can you provide a sample file with just a few sample entries  or if no personal info is involved  screenshots of  the two tables and the fields.

              • 4. Re: Find non-duplicates of related records

                Those kinds of record counts are nothing for a true SQL environment, but with any kind of GROUP BY, FMP will likely take a long time to complete. Not sure without testing, naturally, so as suggested:


                1. Please post a representative data set for each table.

                2. Include the expected output.


                Doesn't have to be a lot of data, just enough to unambiguously demonstrate whatever you need the output to be.


                From what I read so far, I'm thinking this may be a simple SUB-SELECT SQL or something similar.

                • 5. Re: Find non-duplicates of related records


                  Sorry  HAD TO DO IT WITH EXCEL



                  the 2 tables are self joined based on HLQ and ID.  The Portal setup shows the number of occurrence . For 'ID E = 1' and all other are '2'  which is correct. The numbers could be much higher than just 2.



                  But I want to show in list form only those HQL & ID items where the e.g. portal count is the same for all rows.

                  How could I define the Portal count in a calculation   ?

                  • 7. Re: Find non-duplicates of related records

                    thanks for the reminder.


                    I did insert readable items

                    • 8. Re: Find non-duplicates of related records

                      Here is one Option,  I made a Count(ID) field to give the number of records found in your self-join relationship (the portal count)  then I created a second calc field  with  the following eSQL


                      ExecuteSQL ( "Select Distinct ID FROM test WHERE HLQ=? AND C_Count=?"; "" ;"¶"; HLQ; C_COUNT)


                      the output display of this field yields something to the effect of


                      • 9. Re: Find non-duplicates of related records

                        many thanks Brian


                        in my case a SQL is of no help as for detailed analyses it has to be in list form to decide about further actions.

                        Hence using self-joined tables have to be used. This works - however it is a very slow process for 40000 records.

                        • 10. Re: Find non-duplicates of related records

                          Slow process?


                          40,000 records SQL should be nearly instantaneous.


                          In MySQL, for example, a 1,000,000 GROUP BY query takes 0.5 seconds. OK, FMP is not a SQL Database (whatever that means given all the SQL support, SQL Reference Manual, FMP JDBC driver, the lack of any SQL disclaimers in any FMI reference documentation, etc.), but 40,000 record queries should be rocket fast.


                          Can you post your data (or create some similar data with a free tool like RANDOMMITE)?

                          • 11. Re: Find non-duplicates of related records


                            I will provide you with more details ASAP.... takes 1-2 weeks as I am away

                            • 12. Re: Find non-duplicates of related records

                              Take your time. I'll be glad to help if I can.

                              • 13. Re: Find non-duplicates of related records

                                before I am away here a table:





                                The HLQ MAXI points to this related table in LIST format.  Each  'group of names e.g. MAXI.**' must be matched to the 'other group of NAMES e.g.  MAXI.MAINT1.A.APFLOAD; MAXI.MAINT1.A.LOAD etc'.  or vice versa naturally.


                                In above sample, all items related to HLQ MAXI have the same 'AUTH_ID and ACCESS' anything else would be wrong.

                                That means, there could be additional rows pertaining to HLQ MAXI having other MAXI.xxxx NAMEs with more or less rows and/or the AUTHID_ID and or just the ACCESS could be different.



                                Above we have 7 unique NAMES and the same NAME appears 3 times.


                                So my question is what is the simplest way to find out that all 'group of NAMES' are euqal.


                                many thanks

                                • 14. Re: Find non-duplicates of related records

                                  Sorry, I don't quite understand what you mean by "....what is the simplest way to find out that all 'group of NAMES' are euqal.".


                                  So that I and others can follow you, can we try to do a simpler example that's easier to follow?


                                  How about something like CUSTOMER and ORDER?


                                  Create a simple table for CUSTOMER and another table for ORDER


                                  Put in some basic data.


                                  Describe what you want to do in those terms.


                                  If the 1:M CUSTOMER and ORDER isn't right for your example, then change it to be something simple like that. Once you do, everyone can (and will) jump in and help.

                                  1 2 3 Previous Next