Help with Layout including sub-totals by group

[Tony Moss]

I developed a FileMaker Pro database for a friend to enable him to record details of residents at his local workhouse during the C19. He has over 1,900 records to date, including details of Parish and Male or Female (among many other items) for each resident. Since he upgraded his iMac to High Sierra however, his version of FileMaker Pro is no longer supported and he doesn’t want to lash out the £300+ to upgrade it. (Can’t think why!)

I’m using the trial version of Tap Forms 5 to evaluate it on his behalf (with 9 days left). I was VERY impressed with the ease and accuracy with which I was able to import all his records into Tap Forms (via an Excel spreadsheet). It was equally easy to replicate the “Entry Form” layout he uses in FileMaker Pro when creating a new record. So far, so very good.

However, one of his many requirements is to have a layout listing each of the 70 Parishes with total Males and total Female numbers and a Grand Total per Parish. Easily done within FileMaker Pro, but its doing may head in trying to get an equivalent in Tap Forms! Yes, I know the Multi-column List view does give me the gender sub-totals by Parish, but it also gives me one row per resident so no good for his lordship.

I’ve currently got two Forms set up – the Residents Form (1,900+ records) and a Parish Form (70 records – one field – the Parish name). I’ve managed to set up a Join link between the Parish fields in both forms, if this is the way to go.

So, where now? If anyone can help me crack this it’ll be the last step to my forcing him to move to your very impressive product.

Thanks in anticipation,

Tony

[Brendan]

Hi Tony,

Thanks for giving Tap Forms a try for your friend!

What you could do is create a Calculation field which generates a total of the sub-field in the Link to Form field. I’m not sure if you have your Gender field separate or combined though. If it’s combined as a single field to contain all genders, then what you may need to do is create another 2 Calculation fields. One for Males and one for Females. Or perhaps 3, one for Other. Then you could return a 1 if the Gender field is Male and 0 if it’s not. Then for the Female, return 1 if the Gender is Female and 0 if not. The same for Other. Then on the parent form, add a Calculation field for each gender that totals the calculation field you added to the Link to Form field’s form. The net effect of that is to get a list with separate counts for Male, Female, and other.

Your formula for the Male count field on the parent form might look like this:

COUNT::Parent Forms::Male

That would effectively count up the number of 1 values for each of the Male sub-records.

I hope that makes a bit of sense at least :)

In an update I’m working on I’ve added the ability for you to write scripts either at the Field level or the Form level that would let you have much greater control over the resulting value. With the new Script function you would be able to just loop through all the child records, adding up each of the genders and returning a single result that contains the counts of each gender, perhaps like “Male: 20, Female 10, Other: 5”, and so on. Version 5.3 will have that function. You use JavaScript to write the scripts.

Thanks!

Brendan

[Tony Moss]

Many thanks for your incredibly rapid reply, Bendan. I confess that about 10 minutes after submitting my post I did manage to get the child form to work, and include the totals per Parish as required. However, I’ve no idea how I did it, and I’m still failing to repeat my success with other data totals from the parent form.

The parent does indeed have separate fields for Males and Females, populated with 1 or are blank as relevant. Both are plain Number fields in the parent form, and the Total Males/Females fields in the child are set as Calculation fields, but with no formula included. As I said, foe some reason when I added the Total fields to the child layout the link just worked! (Not since, though on another linked form – see below!)

These other data totals I’d like are intended to go on a second linked form (created with Parish Names and linked O.K.) and the data fields in the parent are set to 1 or blank as appropriate. Being a bear of very little brain, I’m not too sure from your explanation as to on which form these calculation fields you refer to should be created – could you help here? Also, you mention the “Count” function – I expected to see this in the function list, but couldn’t find it, otherwise I’d have given it a try first!

The inclusion of a scripting facility will be brilliant. I’m very familiar with FileMaker Pro’s built-in scripting – use it a great deal – but the thought of Javascript, about which I know nothing, fills me with trepidation. Be interesting to give it a try, though, and will enhance Tap Forms tremendously.

Thanks again for your help thus far – I’m so impressed with Tap Forms that I’ll certainly be purchasing a copy when the trial period expires, as a really useful back-up to FileMaker Pro.

Tony

[Tony Moss]

Brendan,

Happy to report that all is well!

As I learned in my Infant school – “Perseverance overcomes all obstacles.” I went back to first principles, took another, logical, look at my approach and realised that I was treating what should have been the Parent form as the Child and vice versa.

Once I’d resolved this, and added the “Link to Form” field to the correct Parent form everything made sense, and I can now obtain all the data I require in the format I need.

Many thanks again for your advice – sorry to have wasted any of your valuable time!

Tony

[Brendan]

Hi Tony,

That’s excellent news! I’m glad that you got things going well now.

Sorry I didn’t reply to your June 2 message as somehow I missed seeing it. But I’m glad you managed to work it out yourself.

Thanks!

Brendan

[Author]

Posts