12 Replies Latest reply on Nov 24, 2013 8:14 PM by Ragupathi

    How use inner join in FM

    Ragupathi

      Hi everyone...

       

      I Just creating the Human Resource Database In Table Fields Employee ID as been Primary Key ,Emp Name ,DOB, Reporting To ,Address......etc..

       

      EMP Id
      EMP Name
      DOB
      Email ID
      Reporting To
      A0001Raj13-10-1988raj@aditya.comS0012
      S0012Arun12-01-1983arun@aditya.comS0001
      A0004varun07-07-1981Samy@aditya.comS0012

       

      Now i want to be Created an Leave Application form

      If Emp Id :A0001 was login portal and he send the leave application to his Boss (S0012) Automatic I Need to fetch His email Id and i need to send the Application to his Boss

       

      Can any one have Idea... to fetch his email id because both or Same table....

       

      if i want in SQL then I can use Inner join Syntax but In Filemaker pro how to accesss

       

      Capture1.JPG

      Base On above relationship i can able to See who and all Working Under me ??

      Eg:

      Emp Id
      Reportess Member
      S0012A0001

      A0004

      A0009

       

       

      Thanks

      Ragu

        • 1. Re: How use inner join in FM
          taylorsharpe

          Assuming you are on Record A0001 (Raj):

           

          Let ( [

           

          F1 = Reporting to ; //  Boss

          F2 = "SELECT \"Email ID\" FROM \"Table Fields\" WHERE \"EMP Id\" = ?" ;
          F3 = ExecuteSQL ( F2 ; Char ( 9 ) ; ¶ ; F1 )

           

          ] ;

           

          F3

           

          )

          1 of 1 people found this helpful
          • 2. Re: How use inner join in FM
            Ragupathi

            Thanks for replying me ...

             

            i want to be Execute in Filemaker itself...

            • 3. Re: How use inner join in FM
              taylorsharpe

              This will work inside FileMaker itself.  Just open the calcluation engine for a field or variable and put this calculation in and it will work.  It is a calculation using an ExecuteSQL function inside of a Let function. 

              1 of 1 people found this helpful
              • 4. Re: How use inner join in FM
                Ragupathi

                If don't mind can you explain me with any example :

                See i want to fetch the data from same table

                 

                Employee #

                Employee Email Id

                Reporting To ( here i will update Another Person Employee Id ) base on this Emp # i need to Search is Email id in the Same Table

                • 5. Re: How use inner join in FM
                  Ragupathi

                  Dear Taylorsharp

                   

                  Can you explain how to dispaly only user who created the records should  display for him

                  it Presently i used View Record ( Limited (Created By = Get (Current Account Name) ) ) so here what will happen is reset all record will Display No access but i don't want to show that record also can you explain for that what i need to do ......

                   

                   

                  Regards...

                  Ragu

                  • 6. Re: How use inner join in FM
                    taylorsharpe

                    You can use a script to go to the first employee, and then store the variables you want, and the Find the new record you want to do something with. 

                     

                    Set Variable [ $Employee_# ; value: TableName::Employee # ]

                    Set Variable [ $Employee_Email_Id; value: TableName::Employee Email Id ]

                    Enter Find

                    Set Field [ TableName::Employee Email Id; value: $Employee_Email_Id ]

                    Perform Find

                     

                     

                    Is this what you are trying to do?  It will find everyone with the same Email as the person in the first record you were on.

                    • 7. Re: How use inner join in FM
                      taylorsharpe

                      You can do a search (or portal filter) that only shows records that he is allowed to see. 

                      • 8. Re: How use inner join in FM
                        pminich

                        The way to handl this natively in FM is to do a self join relationship of your employee table. You want to join the Reporting To field (left side) to the Emp ID field (right side). You can then base a layout on the left side table occurence and pull in the Reporting To field from the right side.

                        • 9. Re: How use inner join in FM
                          beverly

                          yep! Self-join is the word. In Relationship Graph this is a "copy" of the table named differently, to make the relationship connections.

                           

                          In SQL this is also called Self-Join. However the way to achieve that is to make an alias of the SAME table (as you did in the Relationship Graph - but you don't need to actually create the relationship to just use SQL).

                           

                          "SELECT supv.\"EMP Id\" AS supervisorID, hrd.\"EMP Id\" AS memberID

                          FROM "\Human Resource Database\" AS supv

                                   JOIN "\Human Resource Database\" AS hrd

                                       ON hrd.\"Reporting to\" = supv.\"EMP Id\"

                           

                          ....

                          "

                           

                          Notice that I used the "AS" for the same base table to establish alias' to the table for use in the Self-Join. You need to make sure you use the same alias on the field/column references, too. I happened to use "AS" to make alias of the field/column, too, so that I know which field is returned.

                           

                          You won't get the "Reporting to" once per Employee, but at least you'll get the all correctly in the result.

                           

                          IF you have "Reporting to" that might not have employees, then you use "LEFT OUTER JOIN" instead of just JOIN. This returns all "Reporting to" even those without child records.

                           

                           

                          HTH,

                          Beverly

                          • 10. Re: How use inner join in FM
                            Ragupathi

                            Dear Taylorsharp ,

                                                Now i will show the really scenario images IMage1.JPG

                            Now i want to Get Highlight Employees Email Id

                            Step 2.JPG

                            This image shows Reportees who and all working under to Him.....

                            Step 3.JPG

                            Script wrote :

                            Step 4.JPG

                            Variable With explain

                            Step 5.JPG

                            Perfom Find the Reporting Employee ID

                            Step 7.JPG

                            I am trying store the Varaiable value to Feild Temp

                            Step 9.JPG

                            that feild  i am send via Email

                            Step 10.JPG

                            Before Excute the Script

                            Step 11.JPG

                            After Excutering the Script

                            i am not able to come Current Record ID

                             

                             

                            Can you Gudie me to slove this Issues 

                             

                            what i want to do is ? i Want get the Reportee Email Id  Base the Employee Code :

                             

                            Thanks

                            Ragu

                            • 11. Re: How use inner join in FM
                              beverly

                              Ragu, I think I'm a bit confused by this entire thread. Perhaps what you need is the Value List() based on a relationship? What exactly are you trying to return, the names, the numbers, the email addresses for the names? and are you looking for the information on the employee or who they report to?

                               

                              ====== you said:

                              Now i want to be Created an Leave Application form

                              If Emp Id :A0001 was login portal and he send the leave application to his Boss (S0012) Automatic I Need to fetch His email Id and i need to send the Application to his Boss

                              ======

                               

                              IF you have a relationship from the Emp to the boss through the Self-join (on the relationship graph, not through SQL, if that helps), then you can get the information you need on the boss, including the email address. (You can also get this information using the ExecuteSQL function, as Taylor says, but let's try to simplify your process.)

                               

                              Create a copy of your "Human Resource Database" table in the Relationship Graph, but name it "Boss". Then create a relationship join from HRD to Boss using the fields HRD::Reporting To and Boss::Emp Id

                               

                              Now you have a relationship that allows you access to the "Boss" information from any Employee record. You can get the Boss::Email Id field and show on the layout or use in the Send Mail script step or otherwise use however you need.

                               

                              If this works and makes sense, let us know. Then we'll explain how you can create the same function WITHOUT adding the relationship on the graph. This would be a self-join using the ExecuteSQL function.

                              Beverly

                              • 12. Re: How use inner join in FM
                                Ragupathi

                                Thanks lot ... its working fine......

                                 

                                Thanks You Somuch......................