Turning an Excel-based PM tool into a Filemaker Database?

[brownt]

I have a friend for whom I've done a bit of consulting helping him build some project tracking tools in FileMaker.  Helped him through the basic stuff around relating tables together, doing a bit of scripting, creating some basic reports based on data he's entered... Well, now he's hooked!  And would like to do everything for his business in FileMaker.  His current partner does everything in Excel, and has over the years developed a project management spreadsheet he uses to track project funds, activities and generate a financial summary for their clients...  Well, now I've been asked to see if it can be redeveloped in FileMaker, because my friend doesn't want multitudes of Excel sheets for every project, that he has to paw through when he wants to pull data for his other tracking tools that are in FileMaker.   He'd like to be keeping all the data in one place.

His partner's Excel sheet basically tracks the date of an activity, a note about the activity (what business or consultant or company performed the activity), what kind of activity it is (each spreadsheet for each project may have anywhere from 8 to 16 different kinds of activity) and how much money was associated with that activity (credit or expense).  Tracking that is not a tough deal in FileMaker, that piece I've already sketched out.  Reporting on it is where I'm having problems, because he'd like FileMaker to produce a report that looks as close to what the Excel sheet looks like, including columns for each kind of activity they're tracking, with activity totals, balances, budget remaining, etc...  Where I'm really really running aground is the fact that there may be any number of different kinds of activity going from project to project.  Some may have 7 different kinds of activity, some may have 15 or 20. 

I've included a sanitized, example version of one of his Excel files.  I can't wrap my head around how best to do up a report that doesn't have a set number of fields (what would correspond to the columns in his Excel).  I could do it as a great big flat file database and put in fields for every possible kind of project activity, I guess, but.... argh!  

I would welcome any thoughts on how a report like the one I've included as an Excel file here could be created in FileMaker...  Or if the combined expertise here figures this is something that is best left to Excel (I must admit, my thoughts have drifted that direction as I wrestle with this).

 

Thanks in advance!

report.xlsx

[comment]

Caveat: I haven't looked at your file; I've only plucked one sentence out of your post:

46 minutes ago, brownt said:

he'd like FileMaker to produce a report that looks as close to what the Excel sheet looks like, including columns for each kind of activity they're tracking, with activity totals, balances, budget remaining, etc...  Where I'm really really running aground is the fact that there may be any number of different kinds of activity going from project to project.  Some may have 7 different kinds of activity, some may have 15 or 20. 

Filemaker provides no flexibility in the horizontal direction. You can have as many rows (records, sub-summary parts) as your data dictates, but the number of columns displayed must be fixed in advance. Consequently, Filemaker is nowhere near as good in producing cross-tab reports as Excel.

Several workarounds are available:

• you can pre-create the maximum expected number of columns (as fields placed on the layout), and only use some of them on reports that require less;

• you can pre-create a number of layouts, each using a different number of columns, and select the appropriate layout for each type of report;

• you can render your report as text in a text field (while hoping that the tab stops defined in advance will accommodate all types of reports);

• you can render your report as HTML table shown in a web viewer object (arguably the most elegant solution, though not trivial to implement).

 

 

Edited October 31, 2018 by comment

[brownt]

Yeah, that was what I was afraid of.  I did some digging into Virtual List Reporting (https://www.soliantconsulting.com/blog/using-virtual-list-technique-part-1) that looked promising, but I'm a bit too "intermediate" to go all the way with that yet, and I still run into issues with figuring out how it could possibly get the numbers to all add up...  I appreciate your input.  Maybe I'll dig into the HTML table in the web viewer and see how it stacks up against Virtual List Reporting...

Thanks!

Edited October 31, 2018 by brownt

[comment]

13 minutes ago, brownt said:

Maybe I'll dig into the HTML table in the web viewer and see how it stacks up against Virtual List Reporting...

The two may not be as apart as you seem to think.

As for adding the numbers up, I highly recommend you investigate a technique called Fast Summaries, developed by Mikhail Edoshin way back before version 7 and still immensely useful today.