5 Replies Latest reply on Jan 30, 2012 8:46 PM by philmodjunk

    Conditional Field Change on Availability



      Conditional Field Change on Availability


      Hi I am working on a Status system which will show me the statuses if the user has entered the workplans from JANUARY to DECEMBER in all the records, I have a table WORKPLAN with fields ID, MONTH, YEAR and a table STATUSES with fields, JAN, FEB, MARCH.......DECEMBER. What conditional should I apply on a field like JAN so it checks all the records from table WORKPLAN field MONTH and if the word JANUARY exists then it highlights the JAN field red.


        • 1. Re: Conditional Field Change on Availability

          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?

          • 2. Re: Conditional Field Change on Availability

            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. 

            • 3. Re: Conditional Field Change on Availability

              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?

              • 4. Re: Conditional Field Change on Availability

                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

                • 5. Re: Conditional Field Change on Availability

                  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.