It's not the layouts, but the tables we need discuss.
To show monthly totals for each employee does not require a second table with one record per employee per month. A summary report might be used to get one row per employee with a total on a layout based on your attendance table.
But using a Salary details table, you could set up this relationship:
Salary Details::EmployeeID = Attendance::EmployeeID AND
Salary Details::Month = Attendance::cMonth
Month would be a date field with the date for the first day of the month. cMonth would be a calculation field with a date result type: attendanceDate - Day ( AttendanceDate ) + 1
You could then put attendance no. of absents from your attendance table onto your Salary Details based layout. This assumes that this field is a summary field.
For all that you have shared a great detail of potentially useful information, some key details are still missing.
If I understand all that you just sent me in the light of what you have posted originally, you want the number of absences for a given employee, month, year as shown on the "LayEmployees" layout to appear automatically on the "Salary Transactions Detail" Layout.
But as I originally posted, this isn't really a question of layouts, but rather a question of tables. More specifically a question of tables, table occurrences and how they are related. You've posted a picture of your relationships and that is very helpful, but you haven't shown what table occurrences (the boxes on your relationships graph) are the basis for these two layouts, nor the portals on them nor how the Current Month Total Absences No field (Which I assume should be the "source" for the total on the detail layout), is calculated and from what context.
So you have made a good start, but more information is needed.
Ok I am sending more details
Here I am sending a small zip file for details with one or two transactions. In zip two different files (Salary details and Attendance file) but now I make it single file on both file merging.
Thanks Hope this is sufficient to go on details.
Sam Zip.zipx.zip 2.2 MB
Sorry for not replying sooner, but haven't had time this one needs for a proper answer. But now I find that I cannot open your file as my computer does not recognize "Zipx" as a file type for which a default app on my machine will open. Please upload again without trying to compress the file and thus let the forum software zip it. Then, as long as it's not password protected, I'll be able to look at the actual file.
But note that you can also answer my questions without uploading the file at all if you choose to do it that way.
Am I correct that one record in Salary Transations represents the monthly salary for one employee? (One record equals monthly pay for one employee).
Assuming that your answer is "yes", there are two basic approaches that you can use:
a) Use a script to copy the current monthly total from AttendanceDetail_Employee::MonthTotalAbsentNo to Salary Transations::No Of Leaves In Month.
b) Set up an unstored calculation field that automatically computes the same total, but in the Salary Transations table instead of in the Employee table.
The first can sometimes produce layouts that update faster, but you have to set up scripts and script triggers with care or you can get a total absences on one layout that does not match the total on the other. I'd recommend b) unless you get delays getting the Salary Transactions Detail layout to update after adding this calculation field.
The calculation could use ExecuteSQL or you can set up a relationship that matches by month and employee ID to get the total without SQL.
The first thing that you need is a field in both tables, AttendanceDetail and Salary Transations that identifies the month and year and does so with exactly the same data. This need uncovers other problems with your file, the first being that you use more than one way to identify the month and year in different tables. For the same month, Salary Month in Salary Transations will not equal Month_Id_Fk in AttendanceDetail as different values are used to identify the month and year.
So my next thought was to add a calculation field that I've already recommended for identifying the month and year by computing a date that is for the first day of the same date:
Date - Day ( Date ) + 1
But your Date field in Salary Transations is an auto-entered calculation that appears to enter the wrong date as it converts any date entered to the date of the last day of the next month as determined by the date in the previously visited record's date field. You may need that date, but it looks wrong to me and creates an issue for the above calculation. Also, "last visited" is the record last edited by you and may not be the record you want to use to get this date.
This is the auto-enter calculation that I found:
Date ( Month ( Self ) + 1 ; 0 ; Year ( Self ) )
For now, I will assume that the date in this field is correct for the month needed but has the day for the last day of the month. If so, we can add a calculation field named cMonth, with a date result type as written above:
Date - Day ( Date ) + 1
We can then add a cMonth to AttendanceDetail as well:
Day - Day ( Day ) + 1
(I suggest that you consider renaming both the Day and Date fields as you can see that the current names are confusing given that date and day also name functions in FileMaker.)
Now you can add another occurrence of AttendanceDetail, named AttendanceDetail|EmpcMonth with this relationship:
Salary Transations::cMonth = AttendanceDetail|EmpcMonth::cMonth AND
Salary Transations::Emp_Id_FK = AttendanceDetail|EmpcMonth::EmpId_FK
This would allow you to define the Salary Transations::No Of Leaves In Month as a calculation field with this expression:
Count( AttendanceDetail|EmpcMonth::Absent )
Hello Friend ,
I am very much thankful for your kind consideration help on my little project work. Though I have tried your both aspects thoughts but unable to get the result. If you remember three years back you have prepare for me the popover calculator for me . If you don't mind can you give me in sketch writing for the script used for this sync or SQL calculation whatever may be for this. From last 1 hour I am trying but I am not understanding where I am going wrong. From last year I have posted this query in forum but could not get any response.
There is no script used in this example. A script is an alternative approach, but what I described in detail uses a calculation and I provided that calculation in my previous reply.
Note that it requires opening Manage | Database | Relationships, clicking attendance detail to select it followed by clicking the duplicate button (two plus signs icon) to make a new occurrence of that table. You then have to double click the table occurrence in order to get a dialog box where you can rename it to the name I used.
Once that is done, you can set up the calculation fields, set up the new relationship and then put in place the count function to count the number of records for a given employee and month where there is data in the "absent" field.
Making excuse for delay in reply on your above answer. First of all again lot of thanks on your efforts for solving my problem. Though as above I said I have already make and put the calculation as per your answer advice. But the No of leave value (What we get on configuring the calculation) is still Null. No leave value appearing. Her are some pics which I have configure as per your advice.
I don't see any mistakes in your screen shots. You might need to check your data. Make sure that you are really logging the number of days absent in the absent field or you may need to sum the contents of a different field.