Filemaker Proof of Concept

[jagstirling]

Forum Post Text

I am a big fan of Excel have always used it for analysis and reporting but I see the potential of Filemaker (Pro 14 Advanced) and want to see if it really can provide what I need.

I am a complete Filemaker novice, but through the forums I have built up a reasonable level of basic knowledge. Also, I have looked at the many ‘sample’ solutions and videos posted here and elsewhere, which are a great help. A background in Excel modelling has also been a great help as the functions and their logic are often the same, or very similar.

I have decided to try and convert one of my Excel models into a Filemaker solution and would like to post my planned design online, not in order to get something built for me, but in order to;

1. Confirm what I plan to do is the correct approach

2. Identify possible solutions that I could use (for example, I plan to use portals but would like to understand those a little more)

3. Identify source of material specific to my project that may help me succeed in moving from an Excel based model into a more robust Filemaker solution

Please find time to review the simplified detail below, if I can get a ‘steer’ on this then I will add additional functionality myself.

Finally, I have invested a bit of time writing this and providing information as I appreciate that all the responses I receive are free of charge and, that, to get the best responses you need to clearly define the problem. If I have not done that, then please come back to me for clarification, and, if you spare time to read this and respond, thank you in advance.

Apologies if this is posted in the wrong forum > please move if necessary.

Simplified Business Requirement - Background

Company XYZ Inc (for want of a better name) is a global services organisation, with offices in London, New York and Paris. Being a global business, it has a requirement to plan the use of its employees (down to a monthly granularity only) across its clients and offices to ensure that;

1. Employees are utilised optimally

2. Employees are not overcommitted (ie. planned to be working for a client in New York and Paris simultaneously)

3. Employees are not overcommitted in terms of planned work in a month (who can work 45 days in a month?)

4. Forecast work for the coming year is available for management review

5. Analysis of planned work by resource and office is available

As part of the preparation for this project the Resource Planning solution from Filemaker has been reviewed already, bit it does not contain the functionality required.

Also, building from bottom up will be a better learning experience.

Current Solution - Data Entry

Currently this is achieved through an Excel workbook, into which planned work is entered for the relevant OFFICE, CLIENT, EMPLOYEE, DATE (to identify a month) and the WORK planned for that month. A sample is shown below.

 

OFFICE	CLIENT	EMPLOYEE	DATE	WORK	C_DATE
London	ABC Ltd	John	01/02/2016	20	29/02/2016
London	ABC Ltd	John	01/03/2016	20	31/03/2016
London	ABC Ltd	John	01/04/2016	15	30/04/2016
London	ABC Ltd	John	01/05/2016	15	31/05/2016
London	ABC Ltd	John	01/06/2016	10	30/06/2016
Paris	XYZ Plc	Andrew	16/01/2016	10	31/01/2016
Paris	XYZ Plc	Andrew	15/04/2016	10	30/04/2016
Paris	XYZ Plc	Andrew	01/05/2016	5	31/05/2016
Paris	XYZ Plc	Andrew	01/06/2016	5	30/06/2016
Paris	XYZ Plc	Andrew	15/07/2016	5	31/07/2016
Paris	XYZ Plc	Andrew	07/06/2016	10	30/06/2016
New York	QWERTY Inc	Mary	04/03/2016	15	31/03/2016
New York	QWERTY Inc	Mary	06/08/2016	20	31/08/2016
New York	QWERTY Inc	Mary	09/09/2016	20	30/09/2016
New York	QWERTY Inc	Mary	15/10/2016	15	31/10/2016
New York	QWERTY Inc	Mary	30/11/2016	15	30/11/2016
London	ABC Ltd	Peter	31/12/2016	10	31/12/2016
London	ABC Ltd	Peter	03/06/2016	10	30/06/2016
London	ABC Ltd	Peter	05/07/2016	5	31/07/2016
London	ABC Ltd	Peter	08/09/2016	5	30/09/2016
London	ABC Ltd	Peter	03/11/2016	5	30/11/2016
New York	Widget Inc	John	01/01/2016	5	31/01/2016
New York	Widget Inc	John	04/02/2016	10	29/02/2016
New York	Widget Inc	John	13/03/2016	15	31/03/2016
New York	Widget Inc	John	18/09/2016	20	30/09/2016

 

Note, a calculated field is used to convert all the entries in the DATE field into a calculated C_DATE field (using EOMONTH function in excel). This is one so that all entries have a standard date entry for future use. I am aware this can be done in Filemaker.

Current Solution - Reporting

NOTE ; Reporting does not mean the production of a ‘report’ it can (and does) mean the display of information on the screen.

Once the information has been entered the following information is then produced on different sheets using the SUMIFS fucntion in Excel;

Summary by Resource

RESOURCE	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	TOTAL
John	5	30	35	15	15	10	0	0	20	0	0	0	130
Andrew	10	0	0	10	5	15	5	0	0	0	0	0	45
Mary	0	0	15	0	0	0	0	20	20	15	15	0	85
Peter	0	0	0	0	0	10	5	0	5	0	5	10	35
	15	30	50	25	20	35	10	20	45	15	20	10	295

Here we can see that John is planned to work 30 days in Feb-16 (assumption is standard 20 days per week). This would be conditionally formatted in Excel to draw the user’s eye. I am comfortable with this functionality on Filemaker.

Summary by Client

CLIENT	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	TOTAL
ABC Ltd	0	20	20	15	15	20	5	0	5	0	5	10	115
XYZ Plc	10	0	0	10	5	15	5	0	0	0	0	0	45
QWERTY Inc	0	0	15	0	0	0	0	20	20	15	15	0	85
Widget Inc	5	10	15	0	0	0	0	0	20	0	0	0	50
	15	30	50	25	20	35	10	20	45	15	20	10	295

Here we can see that ABC Ltd is our biggest client in terms of planned work for the coming year, with work of avrying amounts throughout the year.

Summary by Office

OFFICE	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	TOTAL
London	0	20	20	15	15	20	5	0	5	0	5	10	115
Paris	10	0	0	10	5	15	5	0	0	0	0	0	45
New York	5	10	30	0	0	0	0	20	40	15	15	0	135
	15	30	50	25	20	35	10	20	45	15	20	10	295

Here we can see that New York is our biggest office in terms of planned work, Paris appears to be a long way behind the other offices.

Planned Filemaker Solution - Data Entry

It looks like a portal is the best way to get the information into Filemaker, something like the example below where new records would be entered manually. I have already read up about value lists and sourcing data from other tables so I have that covered for the OFFICE, CLIENT and EMPLOYEE fields

OFFICE	CLIENT	EMPLOYEE	Jan-16	Feb-16	Mar-16	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	TOTAL
London	ABC Ltd	John	0	20	20	15	15	10	0	0	0	0	0	0	80
Paris	XYZ Plc	Andrew	10	0	0	10	5	15	5	0	0	0	0	0	45
New York	QWERTY Inc	Mary	0	0	15	0	0	0	0	20	20	15	15	0	85
London	ABC Ltd	Peter	0	0	0	0	0	10	5	0	5	0	5	10	35
New York	Widget Inc	John	5	10	15	0	0	0	0	0	20	0	0	0	50

 

I would like some way of filtering the data in the portal, so a user can go in, filter on their office and then edit, delete or add existing records.

Questions

Table relationships

You can see what I have planned from the Current Solution - Data Entry section, do I need to change this? What about 'occurrences'? Do I need a separate occurrence for the portal and one to hold the information in Data Entry. I plan to have tables holding OFFICE, RESOURCE and CLIENT information to maintain integrity (these will not be free entry fields in the portal.

Storing my data in tables

Should my Filemaker Table holding the planning information hold multiple columns, say 12, one for each month and another one for year? So, I would have columns, Jan, Feb, Mar , Apr etc etc and then one called Year into which I would enter 2016, 2017, 2018 etc etc.

I DON’T think this is best practice and I may come to regret this later but it would make it easier to create a portal. Currently I have to try and ‘cross-tab’ (yes I have read this topic in depth) my data without any success. Also, creating a total column (so sum up the months) is proving a challenge.

Summing data in the portal

How can I create a total ‘column’ in my portal? Similar to the ones in the Current Solution - Reporting section, that will sum up the WORK values in that row? This is why I am drawn to storing data in 12 monthly columns, as I have the data now I need to perform something like the SUMIFS function in Excel, using, OFFICE, CLIENT and EMPLOYEE as the inputs.

Future

In the future I plan to add the following features, not sure if this effects the design at this stage;

1. A dashboard charting the data and showing KPIs

2. Skill profiling of individual employees

 

Edited May 5, 2016 by jagstirling

[comment]

How does your current solution handle an assignment that spans across 2 (or more) months?

For example:

OFFICE	CLIENT	EMPLOYEE	DATE	WORK	C_DATE
London	ABC Ltd	John	21/04/2016	15	30/04/2016

where 10 days are in April, and 5 in May.

[jagstirling]

Hey,

Currently I use Excel in the same way as the portal I suggested, with one column per month.

Data is just entered under the relevant month with the appropriate OFFICE, CLIENT and RESOURCE assigned in the columns on the same row.

I understand that for Filemaker, I should have one column for DATE as opposed to 12 columns, one for each month, and then yet another column for the year. This wa sone of the questions I had.

Thanks for sparing the time to reply.

[comment]

Let me start by pointing out two obstacles to what you want to accomplish;

1. Filemaker does not have a SUMIF() function;
2. Filemaker is not flexible in the horizontal direction; for any columnar display, the number of columns must be set in advance.

Now, in terms of data structure, I believe you should have these tables:

Employees -< Assignments >- Offices >- Clients

In order to perform data entry in a manner similar to your current one, you would use a layout of the Assignments table (in List or Table view). However, instead of entering the Clients, Offices and Employees names, you will select their unique IDs - so that a relationship to their corresponding records in their respective tables is established.

I should pause here to say that in a pinch, you could settle for the Assignment table alone, and use value lists for the other three entities. But this will not allow you to store any information that describes a client, an office or an employee, other than their unique name.

Finally, we come to the problem of summarizing the data by month. In the absence of SUMIF() function, my preferred fallback is to use a repeating calculation field (defined in the Assignments table) that splits the assignment's days into 12 month slots (using a global field to determine the year to view). Then use a summary field to summarize each repetition individually, and show (only) the sub-summary values in a layout of the Assignments table that has sub-summary parts by Office and by Employee - but no body part.

 

 

 

 

Edited May 5, 2016 by comment

[comment]

P.S. Actually, the SUMIF() function would not solve the problem of splitting the 15 days starting on April 25 into 10 days in April and 5 in May.  So I am still puzzled how you do that currently in Excel.