1 of 1 people found this helpful
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 )
Thanks for replying me ...
i want to be Execute in Filemaker itself...
1 of 1 people found this helpful
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.
If don't mind can you explain me with any example :
See i want to fetch the data from same table
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
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 ......
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 ]
Set Field [ TableName::Employee Email Id; value: $Employee_Email_Id ]
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.
You can do a search (or portal filter) that only shows records that he is allowed to see.
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.
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.
Dear Taylorsharp ,
Now i want to Get Highlight Employees Email Id
This image shows Reportees who and all working under to Him.....
Script wrote :
Variable With explain
Perfom Find the Reporting Employee ID
I am trying store the Varaiable value to Feild Temp
that feild i am send via Email
Before Excute the Script
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 :
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.
Thanks lot ... its working fine......
Thanks You Somuch......................