Field outputting different values based on location on layout?

[StilLearning]

Background:

FM 9 Pro Advanced

Two primary tables [Delegates & Companies]

Layout is a Subsummary table where "Companies who have registered delegates associated with them, show up on a list, followed by the list of those delegates, with counts for various information about the companies ".

Objective:

I'm trying to get the report to show value of Summary Fields from Company Table in the Layout.

Specifically, to show the Company Count, Meal Pass Count & Booth Count.

Weird:

Nowhere on the layout (which is set-up to display reocrds in the Delegates database), will it allow me to display any of those Summary Fields stored in the Company Field. They all show as blank.

Weirder:

In the Script process, after the Find, I tried to copy the values of the Summary Fields in the Company Table to Dummy Fields in the Deelgates Field.

1. Regardless of the 'Find' Parameters, it always copies & pastes the summary of ALL records in the table. So although as an example it should read 36 for the found records, it always outputs 54 which is the total for all records in the Companies Table.

2. The incorrect value will show up only on the first page in the Title Section. On all subsequent pages, the field appear empty.

3. If you copy & paste that field and move it to the footer, the value goes to 1. I candidly can't tell if it's pulling that from an individual record, or what the case is, but it's odd.

4. There is another numeric counter field in footer that works fine!

Debugging:

1. In describing the above you can likely visualize most debugging steps.

2. Only thing that might be not be self-evident is I double-checked the field placement on the Layout to ensure none were overlapping parts as I thought that might have impacted why the fields were showing false data at bottom.

Any thoughts anyone could provide would be greatly appreciated.

SIncerely, StilLearning.

[comment]

Two primary tables [Delegates & Companies]

Layout is a Subsummary table where "Companies who have registered delegates associated with them, show up on a list, followed by the list of those delegates, with counts for various information about the companies ".

I am afraid that's not a good description. If your relationship is Companies -< Delegates and the layout is based on the Delegates table, then summary fields defined in the Companies table will show the summary values for the related set - i.e. for ONE company. Since you report a different result, it's not clear what you're doing.

[eos]

Assuming you want to get a result like this, you should define your summary fields in the Delegates table, rather than in Company:

[StilLearning]

I am afraid that's not a good description. If your relationship is Companies -< Delegates and the layout is based on the Delegates table, then summary fields defined in the Companies table will show the summary values for the related set - i.e. for ONE company. Since you report a different result, it's not clear what you're doing.

Actually what you say makes a lot of sense as that's EXACTLY how the Ccmpany Table Summary fields are behaving....they keep outputting '1'....which is the counter value for each Company. Instead of the sum of all Company Table Field values.

It sounds like I have to reverse the layout report so that it is based in the Company Table....although when I tried that earlier, my find functions failed to work.

Is there any specific information I can provide that would assist with debugging?

Quick Table Layouts

Company Table

Company Name

Primary Contact

Meal Passes

Meal Pass Sum (Summary Field of all Meal Passes)

Company Counter (Calc Field = 1)

Company Counter SUM (Summary Field of Company Counter)

Etc.

Delegates Table

Last Name

First Name

Special Meals

Company Name

Delegate Counter (Calc Field = 1)

Delegate Counter Sum (Summary Fielf of Delegate Counter)

Etc.

Follow-up Question: Is there no way to copy the value from the Summary Field in one Table at a given time (after a Find) and paste it into the Layout Table in a holding field?

Thanks all....

[StilLearning]

Assuming you want to get a result like this, you should define your summary fields in the Delegates table, rather than in Company:

Yep....very close!

I know what you're saying but my problem is the data that needs to be summarized (counted) resides in the Company Table....as they're company-allocated assets.

Meal Passes as an example will be distributed onsite and are not allocated specifically to a delegate and there are many cases where there are fewer meal passes than attendees associated with the Company (assuming they're going to Subway).

Is there a workaround to get data from a Summary Field in the Company Table into the Delegates Field that I haven't tried?

I tried both "Copy & Paste" and "Set Field" and both have been abject failures. :idot:

[eos]

IIUC, you're summarizing data in one table and are trying to display data which is (or better: would be) summarized in another table. Starting from my screenshot: you don't want to show how many meal passes were given out as the sum of delegates with a meal pass, but as the sum of all meal passes given out to all companies - and that within a summary in the Delegates table. I'm afraid that doesn't work.

What does work is using a self-join instead (Cartesian “x”, any two fields) to count the number of any field (or the sum of values within it) in all Company records. You can e.g. define a calculation field “cSumMealPasses” as Sum (Company_selfjoin::MealPasses ). This field displays just fine on the Delegates layout, since it doesn't depend on the interface, but works within the data structure. You can use the same method for booth or other data in the Company table.

Is there a workaround to get data from a Summary Field in the Company Table into the Delegates Field that I haven't tried?

You won't get the result you want, because a summary field in fact displays several instances of itself in a layout, and the actual content is dependent on the sort order and the group it's within. You can manually copy the contents of a given instance if you click inside, because *you are* accessing a specific instance at a point when it shows the correct value; within a script that's not possible, because you're referring to the field in general, not one specific instance. That's why you get the results you've seen, namely the number of all records.

Have a look at this:

[comment]

I think at this point it would be helpful to have a sample data set and an example of what your report should look like. I don't quite see where in a report of 'delegates by company' would you place the summary of all companies - but then I don't understand the terms being used, either.