Jump to content

Filemaker Proof of Concept


This topic is 2242 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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 by jagstirling
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

This topic is 2242 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.