consolidating data from many records to a large repeating field

[DAVE/]

Hello

I have 2 tables " input" and "report"

In "input" i have as many records needed to make a report but usually around 50 to 100 records.

but in "report" " i have one record with 200 repeating fields .

I have done this so I can sort records different ways in "input"

since ( i don't know of a way to sort fields in repeating fields) then export them in that sort order to the repeating fields of "report".

I am not sure I am doing this the best way. I need to have these records sorted in the first table but in the second " report" table they just need to flow in that order into the repeated fields of one record. Can this be done by lookup or import. and How

Thanks

dave

Edited April 26, 2007 by Guest

[bruceR]

Get rid of the repeating fields. Not sure why you think you needed to use them but they are almost always a disaster. What is it that you feel the repeats are supposed to accomplish for you?

[DAVE/]

it would accomplish having a sort in the first table and bringing that sort to the repeating fields in the second table. That is my reason for this topic.

thanks

Dave

[bruceR]

That doesn't explain anything. Why do you want to use repeats?

[Søren Dyhr]

it would accomplish having a sort in the first table and bringing that sort to the repeating fields in the second table. That is my reason for this topic

Wrong tool, Filemaker is an attempt to be a relational database, which means that everything syncronizing is BUH, data sould ideally reside in one place and the display or presentation of the data as such, has nothing to do with it's actual location, but merely an abstraction to convey it to a certain layout on demand. Snap out of the spreadsheet metaphor or rather the factory floor'ish taking stuff from one box and put it in another.

--sd

[DAVE/]

my client has a report of a whole bunch of inspections they make. We put the data into filemaker but sometimes they need the report chronologically, sometimes they want it alphabetically, and sometimes they want it from a map. So we input the data but we need a way to report it several different ways.

Dave

[Stuart Taylor]

What about a simple table view? (the labels can be clicked on to change the sort order)

[David Jondreau]

What we're trying to say is your whole concept for reporting is off. We can't help you with your actual question, because no one would attempt to create a report this way.

You may or may not need a separate table for reports. Filemaker prints reports just fine from within the data table. However, if you want to save every report ever printed, you may want to move the data to a different table.

Either way you wouldn't use repeating fields for this.

You should provide more information about the structure of your database, ideally post a sample file, so someone can really help.

[comment]

sometimes they want it from a map

That is an interesting requirement, and probably the only one that COULD justify using a repeating field. But, if needed, it would probably be a repeating CALCULATION field - using the data as is.

More details are required, e.g. what exactly do you want to show on the map, do you have a separate table for locations, in short: the whole story.

[DAVE/]

I have included screen shots from the old database. I inherited their report that they like but they just want to be able to sort it somehow. This is for a business that does leak detection on properties. They keep track of the location , type of leak and gallons per day losses. There are between 1 and 200 leak locations and with their header and the footer it can be as long as 4 pages. I have written over the old report in the graphics I am attaching here so you can see what I am trying to loose in the old database. The old file has like 2000 fields in one record. so it never could be sorted. I have a new database with an imput layout that is in list form . this will hold all the leak information I need and then they can do a job number and I can isolate the records for a certain job. These could then be sorted by location, by highest leak , or whatever and this would need to flow into this report like I am showing you. Then this report would be saved as one record. I am including the graphics in this post.

Thanks for your help

Dave

[bruceR]

"The old file has like 2000 fields in one record. so it never could be sorted."

Well 200 repeats can't be sorted either.

"I have a new database with an imput layout that is in list form . This will hold all the leak information I need and then they can do a job number and I can isolate the records for a certain job."

I think we need to know more about this new design.

You really do not want to use repeating fields for this. The inability to sort and isolate is exactly one of the reasons you don't want to do this. Sound like you need to redesign the database and move them away from the 2000 field design.

[DAVE/]

The table view works perfectly and I can sort but how do I put a header and a grand summary on a table view?

Dave

[bruceR]

The header is easy enough. Just display the header. Look at the options for table view properties.

But why aren't you just building a standard FileMaker report?

[Søren Dyhr]

But why aren't you just building a standard FileMaker report?

Let me suggest the answer to this: Metaphoric vanity, should act like a spreadsheet!

--sd

[DAVE/]

If I use the table view it is exactly what I want but I can't put a summary footer on the last page, and I need one.

Thanks

[Ender]

See here:

http://fmforums.com/forum/showtopic.php?tid/186594/post/249656/#249656

[Søren Dyhr]

I've taken a look at your layouts, thought it would be of some help to see a reduction of the number for fields to something like a handfull, eventhough it allows restrations beyond the 200 lines originally thought was enough.

The things should when provisions are there anyway be utilizing a relational structure, in my huble opinion ....here a parent - children - grandchildren kind of structure. Which is radically differnt from your more flat or flat'ish approach soemwhat far from filemakers indtended use - to be fair!

The benefit is that you can make a summary report in the grandchildrens table, that include subsummaries as well as grand... dito.

One trick though is the definition of nine value list wearing only one value each. You have in your original layout used checkboxes, but as I see it ought each leak, only show up under one single type, hence the radio button instead. The key to understanding the template, is to realize where each of the fields in the portal originates from.

I have reverse the "Location" and the type selection, in order to force the creation of the related records more flowish, to prevent the typer of the data jumps back and forth in the reading direction.

--sd

Consolidation.zip

[bruceR]

"If I use the table view it is exactly what I want but I can't put a summary footer on the last page, and I need one."

That's just one reason why you should be building a standard Filemaker report. But you should really get a little more serious about helping us to help you. You have not complied with David J.'s request for a description of your data structure, for instance.

In any case - and especially when asking for free help - you need to be a lot more forthcoming. Copies of the files (without data or identifying client information) would be especially helpful.

[DAVE/]

I appreciate the help very much. I am grateful for all of it. However, my client wants the look but not the buttons and the list but be able to sort. I do not have a file I can show you except the screenshots because the old database was extremely tied to thousands of scripts.

Now I am at this point. I have an understanding of a table and a list form. I see that the table sorts as I would like then I return to the list and it is right. I am not sure there is any way to script going from list view to table view. I would hope so. The trailing grand summary can work with a list view. As SOON as I have something I am not embarrassed by I will put it up. Thanks Again

Edited April 30, 2007 by Guest

[Søren Dyhr]

However, my client wants the look but not the buttons

Ah! Thats where the mapping comes in, take a look at the mods I've made, which is based on this:

http://edoshin.skeletonkey.com/2006/12/crosstab_report.html#more

I wonder if this cures the clients wish to "sort", you can always establish a found set of the entry form, and go from there to the reporting. You could perhaps facilitate your solution with various reporting layouts each having it's own set of x/y pairings!

--sd

ConsolidMapping.zip