1 2 Previous Next 22 Replies Latest reply on Oct 20, 2016 12:38 AM by Atefe

    finding 2 records of 2 table with the same field's Contents

    Atefe

      Hello everybody!

       

      I use FM 13. I have two table in my database and i have related them to each other with their "Field1".

      In order to find two match records (2 records of 2 table with partly same field's Contents), I have create a calculation field in one of those tables with this function:

      If ( Table1::field1 = Table2::field1  &  Table1::field2 =  Table2::field2; "√"  ; "x")

       

      But unfotunately that does not work What is wrong with my function?

       

      I thank you all in advance!

       

      Warm regards,
      Atefe

        • 1. Re: finding 2 records of 2 table with the same field's Contents
          philmodjunk

          & is the wrong operator. You should use the word "And" instead.

           

          And your calculation only compares the values of a single related record (Table 2, apparently). If there is more than one matching record in table 2 (and if this calculation evaluates from the context of table 1), data in the additional related records is not accessible by this expression.

           

          There are other approaches that can do that--such as ExecuteSQL()

          1 of 1 people found this helpful
          • 2. Re: finding 2 records of 2 table with the same field's Contents
            Atefe

            Thank you Philmodjunk for your answer, actually I want to compare just one record of tables to each other, and I have changed the operator with "and". but it does not work again.
            where is the problem you think?

            • 3. Re: finding 2 records of 2 table with the same field's Contents
              beverly

              Your "partly" may be the problem. What do you mean by partly?

               

              If you have two fields to compare (using the 'and') & they are the same '=', your check will be returned. However, if you don't have EXACTLY the same in both fields, both tables, your match will fail. (a space, unseen for example, can appear to match, but are not exact character-for-character)

              beverly

              1 of 1 people found this helpful
              • 4. Re: finding 2 records of 2 table with the same field's Contents
                Atefe

                Thank you Beverly,
                No! by partly I mean there are some other fields in each record, which are not the same. but these 2 fields of 2 tables have the same contents.
                I do not know, whether it helps or not but i should say that: the content of "Table1::field1"  comes from another table!
                I mean it is not a simple text field, it is a calculation filed and looks like this => Field1=  Table3::Field1

                • 5. Re: finding 2 records of 2 table with the same field's Contents
                  philmodjunk

                  You need to look closely at your data and you might just post your corrected calculation here and describe how you are using it. For example, this is not a calculation that you can set up as an auto-entered calculation--it won't update as needed when data changes.

                   

                  But I'd use a relationship that matches both field1 and field2 to find records in Table 2 that match values in both field1 and field2 of both tables instead of just matching by field1 and then using a relationship to match values in field 2. That can fail if you have two or more records that match by field1 but have different values in field 2 because your relationship matches to all those records, but your calculation only references the value of one of them.

                   

                  To add a second pair of fields to a relationship, double click the relationship line to open up the needed dialog, then select a second pair of fields and click "add" to add that pair to the relationship.

                  1 of 1 people found this helpful
                  • 6. Re: finding 2 records of 2 table with the same field's Contents
                    Atefe

                    Hello Philmodjunk,

                    thank you for your suggestion, actually I have thought about your idea, but the problem is that there are not just one field in my layout, which needs this calculation form, also there are much more fields! and when I want to make a relationship for each field, my "relationship graph becomes very large and complicated.


                    Also I write here what i have done =>

                     

                    Table1 has field1, which is a calculation field and recieves its data from filed1 of table3.

                    and also table1 has field2, which is a simple text field.

                     

                    Table2 has  field1 and field2, and both of these fields are simple text field.

                     

                    In table1 I have created a calculation field "checking", with this function =>

                     

                    If ( Table1::field1 = Table2::field1  and  Table1::field2 =  Table2::field2; "√"  ; "x")

                     

                     

                     

                    Best regards,

                    Atefe

                    • 7. Re: finding 2 records of 2 table with the same field's Contents
                      philmodjunk

                      This doesn't change the fact that your calculation, as written won't work for you consistently.

                       

                      Say your data in Table 2 look like this:

                       

                      Field1    Field2

                      Apple     Orange

                      Apple      Kiwi

                       

                      And the current record in Table 1 has this data:

                      Field1   Field2

                      Apple    Kiwi

                       

                      Your calculation will return "x" even though a record with the values "Apple" and "Kiwi" exists in table 2 because your calculation is unable to access any record but the first related record that has the values "Apple" and "Orange".

                       

                      3 options:

                      1. use a relationship that matches by both fields
                      2. Use a calculation with the List function to access all the related values
                      3. Use a calculation with ExecuteSQL to search the table for the specific combination of values

                       

                      2.

                      If ( IsEmpty ( FilterValues ( List ( Table2::Field2 ) ; Table1::field2 ) ); "x"  ; "√" )

                       

                      3. Let ( Query = ExecuteSQL ( "

                      Select Field2 FROM Table2 WHERE Field1 = ? AND Field2 = ?" ;

                      "" ; "" ; Table1::Field1 ; Table1::Field2 ) ;

                      If ( IsEmpty ( Query ) ; "x"  ; "√" )

                      ) // Let

                      1 of 1 people found this helpful
                      • 8. Re: finding 2 records of 2 table with the same field's Contents
                        Atefe

                        Hallo Philmodjunk,

                         

                        Thank you so much for your complete answer and I am so sorry for my late reply. Actually my laptop was broken and I couldn't test it and follow the discussion

                         

                        Also you are right, I had recieved always "x" in calculation result. But now I have used your third option and now that returns always "√" !!
                        I did not used your second option, because it seems that it compare just one fields of tables not 2 fields, though i need beide comparisons. (must Table2::Field1= Table1::field1 and Table2::Field2= Table1::field2)


                        Do you know where is the problem?

                         

                        Warm regards,

                        Atefe

                        • 9. Re: finding 2 records of 2 table with the same field's Contents
                          philmodjunk

                          You must have an error in your query. I suggest posting the actual calculation.

                           

                          With regards to 2) it only checks for the value from field two because the relationship is already matching field 1 values. If the record's field 1 value doesn't match, it can't put it's field 2 value in the list.

                           

                          Meamwhile, 1) remains the most straight forward way to do this.

                          • 10. Re: finding 2 records of 2 table with the same field's Contents
                            Atefe

                            Thank you,
                            Also My calculation is now like this:

                             

                            Let ( Query = ExecuteSQL ( "

                            Select F93000_MOD10 FROM List_of_PDs WHERE Version Rank = ? AND F93000_MOD10 = ?" ;

                            "" ; "" ; Moda::Version Rank ; Moda::MOD ) ;

                            If ( IsEmpty ( Query ) ; "x"  ; "√" )

                            ) // Let


                            Hint
                            List_of_PDs is the name of Table1
                            Version Rank is the name of Field1 of Table1

                            F93000_MOD10 is the name of Field2 of Table1

                             

                            Moda is the name of Table2
                            Version Rank is the name of Field1 of Table2

                            MOD is the name of Field2 of Table2

                             

                            Now I try to do your 2. option.
                            Also, as I said, the first option becomes very complicated.because many filed should be compared to each other...( not only F93000_MOD10 to MOD, but also  F93000_MOD11 to MOD, F93000_MOD12 to MOD, F93000_MOD13 to MOD, F93000_MOD14 to MOD,....)

                            • 11. Re: finding 2 records of 2 table with the same field's Contents
                              philmodjunk

                              Are you on a layout based on Moda or List_of_PDs?

                               

                              Given your query, you seem to have reversed the tables since I am selecting from table 2, not table 1. That makes no difference, but you must be on a layout based on the table supplying data to the ? parameters. If you are, I suggest putting a portal to List_of_PDs on you Moda based layout so that you can compare the actual data in that table when checking to see if it's working correctly as your query looks correct assuming that your context is based on Moda and not some other table occurrence.

                               

                              Also, as I said, the first option becomes very complicated.because many filed should be compared to each other

                              But you didn't say that here in this thread and I did have a response in that other thread that I repeat here:

                               

                              What you describe strongly suggests that you need to split up those fields into a table of related records as this would then make possible a relatively simple set of relationships.

                               

                              And even if you kept your current set up, it's about the same level of complexity whether you use a different SQL Query in each case or you set up multiple table occurrences so that you can match records by different sets of match fields for the purpose.

                              1 of 1 people found this helpful
                              • 12. Re: finding 2 records of 2 table with the same field's Contents
                                Atefe

                                Also actually I am on the layout based on List_of_PDs! you mean i should be in Moda layout ??
                                And ja I have tested with a portal! that works correctly.

                                and now it seems your 2. option works correctly, I am trying to do it with more examples. I hope that stay working

                                • 13. Re: finding 2 records of 2 table with the same field's Contents
                                  philmodjunk

                                  Yes, when evaluating your query, you need to be on Moda. Either that or you need to rewrite your query in order to use it from the context of the other table occurrence. ExecuteSQL itself can be executed from any layout in your file, but how the expressions that supply values to the ? parameters evaluate will produce different values to insert into the query depending on context.

                                  1 of 1 people found this helpful
                                  • 14. Re: finding 2 records of 2 table with the same field's Contents
                                    Atefe

                                    Hello Philmodjung,
                                    Thank you again for your answers. Unfortunatelly the 2. option does not work for all records. I try to explain my talble more in detail with some simple example.

                                     

                                    I have 2 tables, and they are related to each other with their field 1.

                                     

                                    In one record of Table 1:

                                    Field 1=Apple

                                     

                                    Field 2=Orange

                                    Field 3=Melon

                                    Field 4=Banane

                                     

                                    Also In one record of Table 2:

                                    Field 1=Apple

                                    Field 2=Orange

                                    and in another record of Table 2:

                                    Field 1=Apple

                                    Field 2=Melon

                                    and in another record of Table 2:

                                    Field 1=Apple

                                    Field 2=Banane

                                     

                                    now in some calculation fields in the layout of table1, i have used your function as below>

                                     

                                    Checkfield1:
                                    If ( IsEmpty ( FilterValues ( List ( Table2::Field2 ) ; Table1::field2 ) ); "x"  ; "√" )

                                     

                                    Checkfield1:

                                    If ( IsEmpty ( FilterValues ( List ( Table2::Field2 ) ; Table1::field3 ) ); "x"  ; "√" )

                                     

                                    Checkfield1:

                                    If ( IsEmpty ( FilterValues ( List ( Table2::Field2 ) ; Table1::field4 ) ); "x"  ; "√" )

                                     

                                     

                                    Now it seems it can just find the first record, because just the result of first Checkfield is "√" and the other are "x", although they should be "√" as well.

                                     

                                    I thank you in advance for your kind help

                                     

                                    WR

                                    Atefe

                                    1 2 Previous Next