1 2 Previous Next 17 Replies Latest reply on Oct 21, 2015 9:20 AM by APIcoder

    Finding Customer NOT related to other tables

    Tom_Droz

      Hi all

       

      I have a customer table that several other tables can use.  When they associate it, the related table has an entry of the primary key from customers.  The customer table has not entry showing that it is being used in various tables.

       

      From time to time I want to delete a customer but need to make sure that are not being used in the 1/2 dozen tables that might be in.

       

      Is there an easy way to see, before deletion, if any relationships exist?

       

      Thanks for any insight

      Tom

        • 1. Re: Finding Customer NOT related to other tables
          erolst

          Try this:

           

          # [ on Customer table ]

          Enter Find Mode

          Set Field [ RelatedTable1::PK ; "*" ]

          New Record/Request

          Set Field [ RelatedTable2::PK ; "*" ]

          New Record/Request

          Set Field [ RelatedTable3::PK ; "*" ]

          # [ etc. for other related tables ]

          Set Error Capture [ on ]

          # [ not really necessary … ]

          Perform Find

          Show Omitted Only

          • 2. Re: Finding Customer NOT related to other tables
            Tom_Droz

            erolst

            there are multiple TO's of customer tables....

            • 3. Re: Finding Customer NOT related to other tables
              PeterWindle

              RelationInfo might help you here, otherwise, you may want to build a calculation field that checks each relationship with if(isvalid(relatedtable::relatedfield),1,0)  - adding all results and if the result > 0 then there is at least 1 related record

              • 4. Re: Finding Customer NOT related to other tables
                user19752

                There is no 'foreign key constraint' in FM, so it is hard to do, especially if you use the key in the other files.

                You need to list all usage of table/field first.

                For one file, there is a way to using ExecuteSQL()

                • 5. Re: Finding Customer NOT related to other tables
                  bigtom

                  From a layout based on customer table you could use List() to find any related records and be able to identify which records they are in which table. I believe this is TO dependent though.

                   

                  See Example 4:List

                   

                  erolst gave a valid example as well. All of the key data is still in any of the customer TOs so it eally does not matter.

                   

                  ExecuteSQL is another way to do the same thing. I think you can get fields form multiple tables in the same query but that is a little more than I am comfortable with. Or have multiple queries, all the same in the end.

                   

                  So a few ways to get what you need.

                  • 6. Re: Finding Customer NOT related to other tables
                    erolst

                    Tom_Droz wrote:

                    there are multiple TO's of customer tables....

                    Do you mean there is no single TO of Customers that is related to all the other pertinent tables, create a new one that is. Create a layout based on the Customer TO without any fields, and use it with the script above.

                     

                    This would be faster than using calculated fields, and less complicated than converting multiple result lists into Find requests.

                    • 7. Re: Finding Customer NOT related to other tables
                      dtcgnet

                      It sounds like 1) you insert the Primary Key from Customers into a related table when the customer is related to the table. 2) When you're thinking about deleting a customer, you want to know if a record of him exists in any other tables.

                       

                      Create a new TO of Customers, call it "Customer_Check". On your relationship graph, put it off by itself. Create similar TO's for each of the other relevant TO's. Maybe "Company_Check", "State_Check", etc. Relate these TO's to the Customer_Check TO as you normally would.

                       

                      In Customer, define a calculation field of type text. Call it "IsCustomerUsed". The calculation is List ( Company_Check::pK_ID ) & ¶ & List ( State_Check::pK_ID ) & etc.

                       

                      If you ever add another table to which Customers might be related, create a TO for it and add it to the above formula.

                       

                      If the result of the calculation is empty, there are no related records. If it is not empty, there are related records somewhere. Sounds like you don't care where...you just want to know if someone has related that Customer to some other table.

                       

                      To delete a customer, you grab the customer's ID, pop open a new window which goes to a blank layout based on Customer_Check, then if IsCustomerUsed is not empty set $CustomerStatus equal to "Being Used". Close the new window. If the value of $Customer is "Being Used", don't delete the customer. Otherwise, delete the customer.

                       

                      You could also use a list view of the Customer_Check table so you could quickly find any customers who are completely unused in other tables and delete them all at once.

                       

                      Since the calculation is unstored, it would add overhead only when you were checking to see if a customer could be deleted. File attached.

                      • 8. Re: Finding Customer NOT related to other tables
                        Tom_Droz

                        dtcgnet

                        You have expressed the problem well and that is something I will have to consider.  Setting up new TO's seems like a lot of load for so easy of a question.  I was hoping there might be a SQL statement that would return the answer

                         

                        In table  'Customer' the  primary key is: "_ID"

                        In the related  'Vehicles' table  " _SoldToID"  would have the same number from the "_ID" in the Customer Table

                         

                        This same set up is also for purchased from, register to, title to, all from the 'Vehicles' table , There is also a 'Shop' table with the 'Customer' ID

                         

                        Ideally I would like to have the delete button for that customer replaced with a button that would show what 'Vehicles' the Customer is used in.

                        • 9. Re: Finding Customer NOT related to other tables
                          user19752

                          For one table,

                           

                          ExecuteSQL ("

                          SELECT COUNT(*)

                          FROM Vehicles

                          WHERE _SoldToID = ?

                          ";"";"";Customer::_ID)

                           

                          so, make same sql for each table/field and add all.

                          If the result is not >0, you can delete the record.

                           

                          I didn't test that using UNION in SQL make it faster or not.

                          • 10. Re: Finding Customer NOT related to other tables
                            Tom_Droz

                            Hi User19752

                            That is the idea I was thinking...

                            I replaced your data with the actual TO names...  The data viewer will not accept the formula... No sure if the syntax is wrong with the "_"  at the start of the field name or if there is something else.... Thanks for any feedback!

                             

                            ExecuteSQL ("
                            SELECT COUNT(*)
                            FROM Vehicles
                            WHERE _kf_Sold_To_Custome_ID_Second_Name = ?

                            ";"";"";Base_Table_CONTACTS::__kp_Contact_ID)

                             

                            I am also creating a calculation field inside 'Contacts' to evaluate based on the relationship to see if there is a match  So far on my 1st test, that seems to work.  Not sure if I am missing something there.

                             

                            Case ( __kp_Contact_ID=VEHICLES::_kf_Sold To Customer ID ; 1;0)  But changed the "Evaluate from" to the related table

                            • 11. Re: Finding Customer NOT related to other tables
                              dtcgnet

                              Similar to user19752's answer:

                               

                              ExecuteSQL (

                              "SELECT kP_CompanyID

                              FROM Company

                              WHERE kF_CustomerID = ?"

                              ; "" ; "" ; Customer::kP_CustomerID ) & ¶ &

                               

                              ExecuteSQL (

                              "SELECT kP_StateID

                              FROM State

                              WHERE kF_CustomerID = ?"

                              ; "" ; "" ; Customer::kP_CustomerID )

                               

                              Repeat that as necessary as your IsUsed check. You wouldn't need any other TO's then.

                               

                              If you did something similar but used Vehicle Name, then you could put that in as a tooltip to see the list of vehicles the customer was used in, or you could create a new field to do that.

                               

                              ExecuteSQL (

                              "SELECT DISTINCT CompanyName

                              FROM Company

                              WHERE kF_CustomerID = ?"

                              ; "" ; "" ; Customer::kP_CustomerID )

                              • 12. Re: Finding Customer NOT related to other tables
                                dtcgnet

                                When a field starts with a non-alphanumeric character, ExecuteSQL doesn't like it. Put quotes around "_kf_Sold_To_Custome_ID_Second_Name" and it ought to work for you.

                                • 13. Re: Finding Customer NOT related to other tables
                                  Tom_Droz

                                  Hi dtcgnet

                                  Here is the newest one.  It evaluates as "0"  on a layout based on Base_Table_CONTACTS  when it is in use in Vehicles

                                   

                                  ExecuteSQL ("
                                  SELECT COUNT(*)
                                  FROM VEHICLES
                                  WHERE 'VEHICLES::_kf_Sold To Customer ID' = ?

                                  ";"";"";Base_Table_CONTACTS::__kp_Contact_ID)

                                   

                                  I like the concept, but no luck so far

                                  • 14. Re: Finding Customer NOT related to other tables
                                    dtcgnet

                                    Try:

                                     

                                    ExecuteSQL ("
                                    SELECT COUNT(*)
                                    FROM VEHICLES
                                    WHERE "_kf_Sold To Customer ID" = ?

                                    ";"";"";Base_Table_CONTACTS::__kp_Contact_ID)

                                    1 2 Previous Next