subsummary and browse

[crazybake]

I have a row of buttons each which shows a date. When the button is selected the piece of equipment is shown and the total hours are shown through a summary field editable so that when a date is changed. I also have a button that creates a subsummary report that shows all days and hours and works fine... Is there any way at all to have a subsummary report that is not viewed through the preview mode...ie. (in browse mode) editable while viewing all jobs for all days?

thanks

c

[Mike D.]

Is there any way at all to have a subsummary report that is not viewed through the preview mode

Sorry, subsummary reports can only be viewed on screen when in the preview mode.

Mike

[BobWeaver]

Although you can't have summary parts visible in browse mode, you can have calculated fields that use the GetSummary() function to accomplish something similar. You can also use a self-join relationship and the Sum(Selfjoin::SomeNumericField) function to give you totals while in browse mode.

[Razumovsky]

I have used the self join sum method above before, but have not found a way to display the subsummarry data on only the last record of each category (mimicking the preview format). Does anyone know of a way to get a self join running count (without a loop script)? The calc I'm envisioning is something like:

case(count(selfjoin::category) = RunningCount(selfjoin::category), sum(selfjoin::amount), "")

only RunningCount(selfjoin::category)does not exist.

Not critical, but I've always wondered. Any suggestions?

[andygaunt]

Hi,

Try this

Case(Max(selfjoin::category) = category,Sum(selfjoin::amount),"")

This will only display on the last record where the self join::category equals the category (eg, FileMaker = FileMaker)

If you change out the Max for Min, then you can display it on the first row instead of the last.

HTH

[Razumovsky]

pretty slick!

come to think of it, this looks like a variation off the mark duplicates calc I was just reading up on a few days ago in another place in the forum (I think it may have been your post as well). While i trust that it works (will check it out later), I still dont get why Max(selfjoin::category)wouldnt return the same value for all records that have the same category? FM help says that Max "Returns the highest valid value in a field in matching related records specified by (relationship::relatedfield), whether or not these records appear in a portal."-no help there. I just get nervous when using calcs that I dont understand how they get the results they do.

Also, does it have to do with the creation order or position in the found set?

Anyway, I will play with it tonight- thanks for the tip!

[andygaunt]

Hi,

Yes. This is a variation off of the duplicate post in the forum (and yes it was me).

I just thought that if it can see the first record and mark it with a 1 and the rest with a 0 (zero) for duplicates, then why couldn't it find the max (or in this case it functions more along the lines of Last) record and only display the sum of a field on that record. This works based on the creation order of the records, as opposed to their position in the sort order. Will always display on the last record created (or, the last record in the sort order)

HTH

[Razumovsky]

Andy-

Thanks again, but, I still dont understand why it would not display on all records of the same category (I do understand that it does, but why why why?), after all, shouldn't all records of the same category share the same max(selfjoin::category) ? They all share the same max(selfjoin::amount) (at least I think). Am I missing something about the Max function, or is this just one of those things that no one understands but is useful none the less?

Cheers

[andygaunt]

Hi,

This is not so much about the MAX function, but more the self relationship::category to the category field.

All the MAX is doing is looking to the last record, instead of the first (cause that is where you wanted it.)

Case(Category = SelfJoin::Category,Sum(SelfJoin::Amount),"")

This self join relationship sees the first record created as the KEY record and will add the sum to only that record.

whereas

Case(Max(SelfJoin::Category) = Category,Sum(SelfJoin::Amount),"")

will add the sum to the last record only. This is because the Max is now looking to the SelfJoin:: relationship which will take you to the last record.

Am I explaining this ANY better???

Don't focus on the MAX, but on the SelfJoin relationship.

[Razumovsky]

Hmmmmmmmmm

Yes, I think you are explaining it better. I am starting to get the idea- I was under the impression that Case(Category = SelfJoin::Category,Sum(SelfJoin::Amount),"") would never return the result "". I will put this in my pipe and smoke it over lunch.

Thanks!

[ruthcarlton]

I am trying to apply this to a current project...

I have a "sales" file which creates a record for every merchandise item everytime there is an event. Each record has a serial# for the item, a date and a tour name. Totals are calculated thru a self-join relationship based on serial#, but I need to get subtotals based on the tour name.

I tried

Case(Max(SelfJoinItem::TourName) = TourName, Sum(SelfJoinItem::Sold_L), ""), but I must be missing the concept, because it delivers nothing.

[BobWeaver]

I think Andy's formula is missing a step. All items in the same category will by definition have the same category number, so the formula:

Max(SelfjoinByCategory::Category)=Category

will always return true. Rather than referencing the Category field, you need to reference a field that is unique in each record such as a serial number field. So you would need to change the formula to:

Max(SelfjoinByCategory::SerialNo)=SerialNo

Also, if The SerialNo field (or whatever you are using) is a text field with non-numeric characters, the Max function won't work. Better to use Last() like so:

Last(SelfjoinByCategory::SerialNo)=SerialNo

[ruthcarlton]

Still not working...I get nothing at all with the calcualtion

Case(Last(SelfTourName::serial) = serial, sum(SelfJoinbySerial::soldL), "")

To test, I changed the calculation to

Case(Last(SelfJoinbyTourName::serial) = serial, "1", "")

This should put a 1 in the last record where the tour name and serial number are the same, right? It puts a 1 in all three records for a single serial# and nowhere else.

[andygaunt]

Ruth,

First. What is your SelfJoin relationship. This should be the relationship for your tour name::tour name.

If this is the case, then this should work.

It should place a 1 in the last record where the tour name is the same

[ruthcarlton]

The self-join relationship is TourName = ::TourName

I deleted it and recreated it, I'm still getting the same result. The "1" appears only in all 3 records for 1 serialNo.

When I replace the "1" with Sum(SelfJoinbySerial::SoldL), all fields are blank.

[BobWeaver]

There's something wrong with your description. First you don't need a selfjoinBySerial relationship. You only need a selfjoinByTourName based on the TourName field. The Serial number field will be different for every record even for those of the same TourName. So if you have the following records:

TourName, SerialNumber

------------------------------------

Bombay, 00001

Bombay, 00005

Bombay, 00009

Then the formula:

Last(SelfJoinByTourName::SerialNumber)

will produce "00009" for each of these records.

The formula:

Last(SelfJoinByTourName::SerialNumber)=SerialNumber

will produce 0 for the first two records, and 1 for the last one.

If this is how you have it set up, and it still doesn't work, make sure that the result types of your calculations match the field type of your serial number (ie, they either should all be text, or all number).

[Razumovsky]

Andy,

i thought about it some more, and I still dont get it. I also tried experimenting with the formula, and with no success at all. I found that not only did Case(Max(SelfJoin::Category) = Category,Sum(SelfJoin::Amount),"")produce no data for any record, but that also Max(SelfJoin::Category)was blank for every record as well. I tried these as text, number, date, and container (for the heck of it) calcs, and they were all based on the selfjoin relationship category::category. I use FM5.0 on a PC Win ME (lucky ME), although I'm not sure how that effects things. I am beginning to suspect that your computer was bit by a radioactive klezworm as a teenager, endowing it with supernatural powers any chance you could post a working sample of your calc? If there is some way to make it work, would really be a cool feature.

-edited below-

Ruth,

Untill this gets resolved, I was going to propose a work around using a loop script that just got its butt whupped by Bobs much simpler idea posted above.

[andygaunt]

Razumovsky,

no radioactive worms in here I promise you.

OK. Sample file time to prove it. Here you go. (see attachment)

TourNames.zip

[Razumovsky]

Righteous!

The calc in your file does work indeed, but is slightly different then what you had posted above. based on the selfjoin relationship category::category, the Max refers to selfjoin::recordID, a different field. The problem was that the above calcs used Max(selfjoin::category), the very same field that selfjoin is based on, giving it always a nil result (at least for me). The resulting calc if essentially the same as Bobs idea above.

Cool tip, thanks for sharing.

[andygaunt]

Hi Razumovsky,

Ooops. Sorry about that. Glad the sample file worked. And yes, it was my bad on displaying the category field as opposed to the recordID field. I just got caught up in the explanation.

All's well that ends well.

[ruthcarlton]

In order for the solution to work, the serial#'s must be unique. In my file, there are 32 serial numbers each representing a different piece of merchandise. I guess I need a subtotal of a subtotal.

[ruthcarlton]

Just for closure...

I found a simple solution by concatenating TourName & SerialNo, which allows me to use the calculation Sum(SelfJoinbyTourSerialNo::Sold).

Thanks for all the help, I learned a lot through this thread.

-Ruth