Headscratcher: summary field for filtered portals in sub summary parts

[Neuron]

Alright, it's simple.

 

I have a solution for newspaper delivery.

 

I have 3 tables: location, client and dropData.

 

Location is information about the place to put newspapers (e.g. Bob's corner store, Yummy Pizza Shop, etc), client is each newspaper (Herald, Express, etc), and dropData is how many copies to deliver.

 

I've got location and client related via a locationID, and dropData and location related also via a locationID.

 

Then I've got a report for the delivery person that shows location records, sorted by route (multiple routes per person).

 

I have a portal on each row showing only the dropData for each newspaper, but I can't for the life of me figure out how to sum the drop amounts by client to put at the top of each route on the delivery sheet (so the person knows how many copies to bring at the start of their route).

 

Have I set this up all wrong, or am I missing something simple?

[Lee Smith]

Automatic message

This topic has been moved from "FileMaker 13 General Discussion" to "Portals".

[eos]

The method to display summarized data for a filtered portal is to use a one-row portal filtered by the same filter predicate(s), displaying a summary field (like sTotalCopies) from the portal TO.

 

OTOH, if I understand correctly, DropData is a join table between locations and clients. If so, you can base your report layout directly on DropData. 

[comment]

Alright, it's simple.

If it's simple, why is this a "headscratcher"?

The truth is, this is not simple at all. You have conflicting requirements that cannot be satisfied (using conventional reporting techniques) in a single report: on the one hand, you want a report summarized by:

 

• Route

• Location

• Client

 

On the other hand, you want a report summarized by:

 

• Route

• Client

 

 

One way to handle this is to produce two separate reports. Another way is to produce the report for one route at a time, placing the current Route's ID in a global field in the Clients table, and using an auxiliary relationship in the form of:

 

Clients::ClientID = DropData 2::ClientID

AND

Clients::gRouteID = DropData 2::RouteID

 

to enable each Client record to sum its own quantities for the chosen route. Then you can show these quantities in a portal to (another occurrence of) Clients, using the x relational operator to show all clients.

 

 

Note that this method sums over a relationship and thus ignores any found sets; this may cause a problem if your report needs to temporarily suspend a location from a route, for example.

[Neuron]

@eos - your first solution is what I have done in many other parts of the solution, but it doesn't work in this case for the reason @comment mentions: I'm trying to have to different types of summary on the same report.

 

Your second idea may work, if I can get it to only show one row per location still instead of one row per dropData record (since then each location will show up many times, wasting space and being confusing to use and read). Maybe some kind of subsummary-as-body technique?

 

 

@comment - I was thinking the same thing and I think you've hit the nail on the head. I am trying to show two different kinds of summary on the same report. Two separate reports won't help here, since the drivers need to just have their one 'master sheet' with all relevant data on it.

 

Your second solution also won't work, since I need it to apply to the found set only (there are lots of locations/dropData records that apply to other routes, which need to be excluded from the counts for the current route, as well as the other problem you mention of inactive stops.

 

 

So perhaps I need to cheat it somehow? I can have a bunch of extra relationships that specifically filter what I want, and show those numbers on the report? Or maybe a script that generates the report by doing a find on each set of records, then storing the foundCount in a variable, then showing all those variables on the report? It wouldn't be 'live' data, but since these turn into printed sheets it wouldn't have to be. Lots more to set up on my part though, which is why I was hoping for a simpler solution.

 

Any other ideas from the community on how to achieve this?

[Neuron]

Alright well I've tried all of the solutions listed with no luck, still can't get the right numbers to appear where I need them.

 

Should this actually be so difficult??

 

Is it the way I've structured my tables and relationships?

 

To do what I describe, how would you guys set up your tables and relationships?

 

Remember all I need is to be able to show a report that lists a found set of locations, once each, with the dropData for each of those locations (so each line has dropData for every client) as well as a grand summary of stopData delivery amounts listed per client (so the delivery person knows how many copies of each client's paper to take when them on their route).

 

At this point it's seeming like maintaining a basic spreadsheet would get me where I want to go about a hundred times faster, but doesn't that seem wrong?? Isn't the whole point of FM to be much more powerful than a spreadsheet? Why am I having such a struggle getting it to do something so seemingly simple?

[comment]

Your second solution also won't work, since I need it to apply to the found set only (there are lots of locations/dropData records that apply to other routes, which need to be excluded from the counts for the current route, as well as the other problem you mention of inactive stops.

Locations/dropData records that apply to other routes will not be included in a relationship that is based on matching RouteID. As for inactive locations, you might find a way to exclude them from the relationship too - it depends on how they are designated as inactive.

If that cannot work, I would suggest you move to a scripted solution, where you first sort your records by Route and by Client* and write down the sub-summary values for each route in a field or a variable; then produce your regular report* and show the previously obtained results in a sub-summary by route part. For this purpose, a technique known as FastSummaries (do a search) would be most convenient.

 

This is assuming you are not yet using version 13.

---

(*) Both reports should be produced from the DropData table, using sub-summary parts - not portals.

[Neuron]

@comment Thanks for the reply, but ultimately I decided to abandon the 'proper' setup for something much easier to do.

 

That is, since there are only a few newspapers in my town, and since new newspapers are a rare thing to see, I've deleted the dropData table and just incorporated the delivery amounts directly into the locations table.

 

It feels 'wrong', since I now have an extra two fields for each newspaper in each location record (copies to deliver and then a summary of same, to use on the report) but it gets me where I need to go.

 

I can now use location records in the report, show each location's deliveries per newspaper on that row (since it's just the new unique fields I added), and then show the new summaries in the sub summary parts and grand summaries.

 

Wish it was a simpler beast, but I think for the intended use case and foreseeable future this is the best use of my time.

 

As an aside, how would your suggestion be different for version 13?

[comment]

I've deleted the dropData table and just incorporated the delivery amounts directly into the locations table.

 

That's a pity, because it's not that difficult to do it right. But it will work. And if you want to make it slightly more elegant, you can use a single repeating field for the quantities and a single summary field to summarize the repetitions individually.

 

As an aside, how would your suggestion be different for version 13?

That's a good question. I had an idea using the new option of a summary field to list the values in a field, but I don't recall the exact details ATM. You could very likely do something similar using a recursive custom function in v.10 Adv. too - but IMHO a scripted solution would be more robust.