6 Replies Latest reply on Mar 3, 2014 11:48 AM by RayGerman

    Extracting Values list information from a field



      Extracting Values list information from a field




           I am working on a leave request form in Filemaker.


           I have created a field called “Purpose” with a Value list 





           Without pay


           I also have another field called Hours Requested. The end result would be an employee could request 8 hours of Sick Leave, Vacation, etc…


           Everything is working great, but I would like to have a report, where accounting can view each employee and be able to see sub-totals of how much sick leave, vacation, etc… each person took in a calendar year.


           I was thinking I would need to make a calculation field to separate the Purposes for each record so I could then put it in my sub-summary report, Attached is a calculation field called paid_time_off_in_hours Ibut it is not yielding the correct results.  Anyone have any ideas, what I am doing wrong?


        • 1. Re: Extracting Values list information from a field

               What you have defined is a Boolean expression--an expression that will return 1 (True) or 0 (False) when it is evaluated. It is comparing the value of the field named Purpose to the value of "Sick" & paid_time_off_in_hours. The & operator concatenates (combines) the text "Sick" with the value of the field.

               So that's not a calculation likely to produce the results you expected to get.

               But you don't actually need any such calculation fields to get the needed sub totals if you set up a typical summary report. I am assuming that each requested leave is a different record. I am also assuming that you have a single field that records the amount of time off requested.

               If so, you can define a summary field to compute the total of that requested time off field and you can sort records by Employee, then by Purpose to produce a report that looks like this:

               Employee: John Smith
                  Sick                   xx

                  Vacation            yy

                  Balance             zz

                  Without pay       aa

               Employee: Jane Doe
                  and so forth....


               This report is a list view layout with two sub summary layout parts and no Body layout part. The first sub summary layout part contains the Employee Name and has specified that it is "when sorted by Employee". The second sub summary part has the purpose field and is "when sorted by Purpose".


               Note that if records are not sorted by a sub summary layout part's "sorted by" field, the sub summary part is not visible. And the order shown for purpose would only be possible by setting up the sort order to use a custom sort order based on your value list.


               To limit the report to data for specific employees or specific time periods, you would perform a find that only pulls those records into the layout's found set before sorting them by both employee and purpose.

          • 2. Re: Extracting Values list information from a field

                 Hi Phil,

                 I am new to reporting and summaries, so bear with me. With your solution above would I be able to build a report that shows a Total hours taken of only Sick leave, and a total hours taken of just Vacation leave, And a total hours taken of just Balance day per employee. The way I have it now, I just get a total of all hours taken and does not break down the sub-totals per Purpose.  Although it soes show a list of the hours and which purpose 

                 My goal is to show person "X" took 12 hours sick leave, 40 hours vacation, 12 hours Balance Day, for the calendar year of 2014.

                 Right now my total would just shows 12+40+12 = Total 64




            • 3. Re: Extracting Values list information from a field

                   Yes. That's why you place the summary field inside a sub summary layout part. That's what enables the summary field to display a subtotal--the total for just one purpose for one employee.

              • 4. Re: Extracting Values list information from a field

                     Hi Phil,

                     OK, I think I am starting to understand the sort in reporting. I created a layout called Simple Report and added the fields you suggested but it does not appear to be giving me the totals for each my value list entries, Sick, Balance days, etc... I posted the file. It does not have any password. 

                     It should be reporting I have taken 24 hours in balance days and 20 hours in sick but my SImple Totals Report is only reporting 8 Balance and 8 Sick.




                     Any thoughts?




                • 5. Re: Extracting Values list information from a field

                       You didn't use the summary field on your layout.

                       Replace <<Paid time off in hours>> with your summary field. (No real need to make these merge fields by the way.)

                       And this summary field cannot be a running total summary field, you'll need either a new summary field or you'll have to clear that option.

                       And I'd recommend sorting the records by an EmployeeID field that uses a number to uniquely identify each employee, you could get two employees with exactly the same name. You can sort by employee last name, first name to alphabetize your list, but then make the next field in your sort order EmployeeID to make the sub summary layout part visible.

                  • 6. Re: Extracting Values list information from a field

                         This is working great!!! What a powerful tool!!  Phil you are the best!!