Can you describe tables and relationships in a bit more detail?
From the context of what record in what table are you checking this status field in Workplan?
Phil, I am using two tables WORKPLAN and STATUS
Now WORKPLAN includes fields : id, month, year
and STATUS has fields : JAN, FEB, MARCH............DEC
Now as the WORKPLAN table will be having 12 records (Jan to Dec) and in the STATUS table I want to make each field highlighted in green if all the months are entered in the WORKPLAN records and if not then the rest should remain red.
Yes, but how do you relate records in Workplan to records in status?
Do you have this relationship?
WorkPlan::WorkPlanID = Status::WorkPlanID
And if so, how is WorkPLanId defined in each table?
Yes surely the relationship is WorkPlan::WorkPlanID = Status::WorkPlanID, Phil as you have been supporting me very well, kindly this is what i basically want (I am sure you will have a better idea)
I am working on a Workplan database and the user has to enter his/her upcoming month workplan by 26th (11:59 am) of every month and then click the submit button to automatically email the entered workplan for the month. I want the database to automatically email me on 27th (12:00 am) the status if the workplan for the upcoming month was entered or not, on this basis the salary of the employee will be stopped and I also want to see an overall status summary of all the previous months for which he has entered the worklplans.
Below is just a sample screen for what I want for Status Summary
First to get a relationship that works for this. Do you have FileMaker 11? (That makes filtered portals an option here.)
Each employee submits a monthly work plan so a relationship needs to be specific to the employee and (if not handled by a portal filter) possibly the month and year.
At the very least, you will need this relationship:
WorkPlan::EmployeeID = Status::EmployeeID
If you have FileMaker 11, you can set up 12 filtered portals to status with a different filter expression for each month. You'll need either a date field in Status to record the year and month (can use 1 for the day in all cases) or you can have two number fields, Status::Month and Status::Year. A number field, submitted, in status can be used to indicate that a workplan was submitted with 1 indicating that the plan was submitted.
Then your portal filter expression for the January portal can be:
Year ( Get (CurrentDate ) ) = Year ( Status::WorkPlanDate) AND
1 = Month ( Status::workPlanDate )
a conditional format on the WorkplanDate field (With data formatting to only display the month name or it's three letter abbreviation) can change the fill color if the status::status does not equal 1.