Checkbox Summation - Excel to FileMaker Pro 9

[Aventura]

If this has already been answered, feel free to redirect me to the correct thread. I could not find it through a search.

We are migrating our information from Excel to Filemaker Pro 9. One of our commonly used forms is a Percentage of Completion Inspection form for some large items we manufacture. We use this weekly to track the percentage complete status of our long projects.

For example:

CAD Drawings Printed: 4%

Materials Ordered: 3%

Frame Assembled: 6%

Electrical Components Installed: 8%

I want to place a checkbox next to each of the approximately 40 line items of the project. I then need a summation box at the bottom that will show the running total of only those boxes checked. For example, had I checked all 4 line items above, we would know our project is 21% complete; had I only checked the first 3, we would be 13% complete, etc.

In Excel, we placed a checkbox in the cell adjacent to the line item, linked the True/False in the next cell, and the percentage in the next. Using an IF/THEN formula created the running percentage total we need.

I understand value lists, I understand checkboxes. I do not understand how to write the "IF the checkbox is marked, THEN sum the percentage into the total..." in FileMaker Pro language.

Any ideas?

Thanks in advance,

Joel

[Vaughan]

If( not isempty( checkbox field ) ; number )

You'll need another field to add these conditional numbers together for the percentage.

[Fenton]

If these are truly "line items", that is related records in a separate line items table, and you have the "percentage" in a number field, as [4], [3], etc., then would be a simple matter of adding them up, from their parent table.

Since you want to do it as a checkbox, to make it easier to check off, then you need a couple more little fields in line items.

A Done_flag field, which will be a number field, formatted as a checkbox value list, with a single value of 1.

A calculation field,

_cDone_percent = Case ( Done_flag > 0; Percentage )

Then, from the parent table:

Sum ( line items::_cDone_percent )

The above is almost exactly what you did in Excel, just different terms, and relational design. If you try and do it "flat", all in the parent table; well, it would be a PITA.

(Actually it wouldn't be all that much of a PITA, as you could use PatternCount() to see if an item was checked in a checkbox of text values. But it's not the best method I don't think. Especially if you ever want to further automate or report on those percentages; not to mention change the percentages; they would have to all be hard-coded into the calculation.

I tend to do things relationally. It is just more flexible. On the other hand, maybe you want to do it flat. Maybe you want it hard-coded and fixed. Maybe you don't want to expand it in any way, ever. I don't really know.

The term "line items" implies related records, or a repeating field(s), in FileMaker anyway.)

Edited August 27, 2007 by Guest

[cjaeger]

And if it's only one field:

Evaluate(

Substitute(

Filter(Checkbox Field;"0123456789¶") //filter out everything but numbers and returns

;"¶";" +") //substitute returns with plus sign

) //evaluate the calc

& "%" //add a percent sign

[Aventura]

Fenton,

Thanks for the reply. I plugged it in as you said and it is exactly what I am looking for; two questions remain:

(1) I set up the line item table as a separate table and added the fields you suggested. I then pulled it back into the parent table as a checkboxed value list. The values and checkboxes show, but when I check them from the parent table, nothing changes in the sum field underneath (on the parent table).

Which leads to:

(2) The SUM field (on the parent table)is a calculation field, correct? That's what I currently have it defined as, but it doesn't show any values when I check the line items from the parent table.

What am I doing wrong?

[Fenton]

I don't think you did your "checkbox" relationally. It is not in the parent file. Only the sum() total is in the parent file.

I also would do the "tasks" for the items as their own "reference" table. This has 2 advantages.

1. If they ever decide to change the name of an item, it will not mess up reports on earlier jobs.

2. Using the names kind of bloats the line items for no real reason.

3. It lends itself to further automation, such as schedules, etc.

4. I'm just a relational fool :-]

The disadvantages, which I consider minor, are:

1. You see the IDs

2. You would not need to see the IDs, or enter tasks for a Job, if you imported the whole set of Tasks into each new Job, using a "New Job" button with a script.

But anyway, this is how it would be done totally relationally.

PercentCompleted.fp7.zip

[Aventura]

Fenton,

Excellent work.

Your example file cleared up my confusion...how much do I owe you ?!??!? :

You were right, my task checkboxes were in the wrong table. Hence the summation not calculating correctly.

Thanks again for pointing me in the right direction.

[Aventura]

Ok Fenton, I need your help again---

2. You would not need to see the IDs, or enter tasks for a Job, if you imported the whole set of Tasks into each new Job, using a "New Job" button with a script.

How do I do that??

They have now decided they want to see the whole list, as it emphasizes not only what IS done, but also what IS NOT done.

Go figure.

[Fenton]

Yes, that is a good reason to Import the Tasks into the JobTasks. I added a "New Job" script to do that. Actually I added 2. Because there's 2 methods to do this.

1. Import the records, then Loop to set the parent ID.

2. Set the parent ID into a global in Tasks, then Import that along with the Tasks.

Either way works. Method 2 is better if you have a lot of Tasks; but you don't.

The Import step can be a little touchy if you change the file's name. Or if you Import the entire script into your file (which you should do). I've added a step to set a Variable to the current file's name, and used that ALSO in the Import path(s). But you should check/change that Import step's 2nd file path and check the import order if you move it.

Get used to the idea of checking Import steps. If it's wrong, it can bring in a lot of data incorrectly in a hurry. But it's a good method to bring in a lot of records.

[ Just thought of another method ]

3. You could do it all with a Loop also, using the global JobID, and a relationship with "Allow creation of related records" from Tasks. That's actually a good method, but maybe a little confusing.

You could extend any of the above methods; to pre-pick only certain tasks for a job. Or (better) assign a Type to a Job, create a join table or multi-line Type field in Tasks (some tasks go with multiple job types). So after choose the Job Type, it imports only the tasks relevant to that type.

PercentCompleted_fej.fp7.zip

Edited September 1, 2007 by Guest

[Aventura]

Nice work Fenton, worked like a charm...

Thanks again for making me look smarter than I really am!!

[Fenton]

We all depend on others for some of that -]

[LaRetta]

Hey Christian!

I agree with Fenton (and I've no doubt you do as well) that relational is the most flexible and recommended method. But I must tell you that your solution is very sweet. I like this calc a lot! Thanks for making the suggestion! :wink2:

[Aventura]

Hello Fenton,

Another tweak regarding your script:

I have a table named PROJECT as the parent table to JOB. PROJECT contains some project information as well as a portal showing the JOB inspection records from week to week. I can create new records in JOB through the PROJECT portal, but it does not run the New Job script, and therefore does not import the job tasks.

How do I instruct Filemaker to run the New Job script from the parent PROJECT portal?

[Fenton]

I would say adding a parent table is a little more than a "tweak" :-]

Basically it sounds as if a Job is ALWAYS a child of a Project. In which case the only way you can create a new job from Jobs itself is to create another job for the same project. Because there's no easy way to choose another project, and no compelling reason why it would be required, since a new job can be created easily from Projects.

To create a Job from Projects you just need to capture and add the ProjectID to the Job. There is a question of "where do you want to end up" however; Projects? (where you started) or Jobs? That's just a Go To Layout choice, as all the work has already been done.

You also need easy navigation between a Project and its Jobs, and visa versa.

PercentCompleted_fej.fp7.zip

[Aventura]

Thanks for the fix....

My database is now up and running as I'd like (so far anyway...) This is far superior to what we were doing in Excel and exactly what I was hoping to achieve.

Fenton--I can't thank you enough for your gentle guidance during my first foray into Filemaker. I'm hooked!!