SUM and COUNT for portals

[Raizel]

Tried using Google and haven't been able to apply solutions to my portals. Now that I have one portal filtered into two, I need to revise the summary fields to count entries and total the time.

My attempt:

1872:00:00 = Sum(SideAB TimeCalc::sideD_TIME)
should be total time of portal "A"
13 = Count (SideAB TimeCalc::sideD TITLE) (because there are 6 in the portal "B" not shown)
should be 6 total count of portal "A"in mm:ss format which isn't working unless I use hh:mm in which case less than one minute fails

The same solutions for this side will be applied to the other portal. Then, I need two fields that combine the results for total count and total time of both portals.

[comment]

For the counts, define a summary field in the portal table that counts the Title field (or any other field that cannot be empty, such as the primary key field). Place 3 instances of this field on your layout: one inside a one-row portal filtered to show only side A tracks; one inside a one-row portal filtered to show only side B tracks, and one directly onto the layout, outside of any portal.

For the sums, do the same thing with a summary field defined as total of your Duration field (which is hopefully a Time field).

 

[Raizel]

Fields in portal:  TimeCalc::sideD_Time - TimeCalc::sideD_Titlte - TimeCalc::sideD_Artist - TimeCalc::sideD_Comments

Field inside portals: Count (sideD TimeCalc::sideD TITLE) but failed. Same field outside portal gives 13.

Haven't tried the sideD_Time yet.

[comment]

Have a look at the attached demo.

--
P.S. "TimeCalc" is not a good name for a table. Name your tables by what each record represents. And I believe you can drop the "sideD" part from the field names; IIUC, there is no side D - and a title is a title, whatever the side is. 

AlbumSides.fmp12

[Raizel]

the table "TimeCalc" was part of the original reason it was created. So, like sideD -- that's temporary until I get things working and ready to configure dB into its new revision.  Thanks for the sample, will study how you configured and translate it over to my setup.

[Raizel]

Fantastic. The attachment was nothing like I interpreted the instructions. Thanks.

I'm finding my time field formatting is probably to blame with results for total time per side. Using "Abbey Road" from your example, my displayed time is 12:45 but when clicked it shows correct at 24:45 and 10:09 for 22;09. The individual times are formatting incorrectly too but total works. (times will vary depending upon source of data).

At one time I switched to hh:mm because I couldn't get time to display as desired any other way. While fields are now not set, the formatting remains.

[comment]

It sounds like you have entered the time values as hours and minutes instead of minutes and seconds. You must enter the values with an explicit zero for the hour, e.g. "0:4:20". An entry of "4:20" will be interpreted as "4:20:00".

[Raizel]

Individual time is 03:38:00. If I try to change to 0:3:38 it displays as 00:03. Only clicking will show the :38 (field is wide enough). Trying for 03:38 (mm:ss) or if < 1 minute 00:38.

[comment]

9 minutes ago, Raizel said:

Individual time is 03:38:00

That's what I am saying: 03:38:00 is 3 hours and 38 minutes. If you want 3 minutes and 38 seconds, then enter 0:03:38. Then format the field to display as mm:ss.

Keep in mind that when you click into the field, you see the data "as entered", with the formatting removed.

 

[Raizel]

2 hours ago, comment said:

It sounds like you have entered the time values as hours and minutes instead of minutes and seconds. You must enter the values with an explicit zero for the hour, e.g. "0:4:20". An entry of "4:20" will be interpreted as "4:20:00".

When I enter as 0:2:30, I get 12:02:30 set to mm:ss. If I don't set in Inspector I have to enter as 2:30 (equiv to 2 hours:30 minutes). I looked at your file you sent and see it does work but wonder if so many attempts over so many months has made a mess of my data.

[comment]

1 hour ago, Raizel said:

When I enter as 0:2:30, I get 12:02:30 set to mm:ss.

No, that is not true. If you have entered 0:2:30 and the field displays 12:02:30 then the field is formatted to display as h:mm:ss, 12-hour format, with no AM/PM indicators.

Do note that the formatting affects only the display, not data entry. 

 

1 hour ago, Raizel said:

wonder if so many attempts over so many months has made a mess of my data

I don't think so. I believe you simply have the wrong data to begin with.