Dynamic Reporting, Multiple DB's

[yafreax]

I did not see anything llike this mentioned around here so i will post here and let you guys take a look. This idea was taken from the Task Manager solution that Filemaker released. Basically, the idea is to have a reports database that stores information for reports and this allows users to create and save their own reports to use at a later time. This potentially saves a great deal of time for the users if they have a number of reports that they will use often.

The problem with the Task Manager, though, is that the reports were only for one database, using two different layouts. The project i am working on now (which will be an updated / revamped version of an invoicing system) will have multiple databases that requiring reporting. I put this sample solution together that allows dynamic reporting of multiple databases. DL it and have a look. I built them in FM 6 on a Mac, but zipped it for easier Windows compatibility.

The issues i would like some help on:

1.) I've had no real training and i haven't taken the time to read a lot of the advance technique guides, so I always have the feeling i'm doing things the long way. Is there a simpler way? One of the main issues with this is that as the solution grows, the more work there is involved. In order to do this the way i'm doing it I have to set up a.) calculations for the criteria field tags b.) additional criteria fields if more fields are needed c.) scripts that match the criteria with the correct fields in the respective databases. d.) a manually updated value list that stores all the names of the databases in the solution ( i cannot use the Design Function for this because our users will have databases open that are not part of this solution).... if you look through the solution, you'll see what i mean.

2.) I'd like to add a sort field option rather than have the Sort Dialog pop up. You can see part of the work there as it is. The sort field runs a quick script to update a value list based on field names that are placed in the "SortList" layout that must exist in every DB. My thought was to have a value list in each DB that was based on the g_SortField Field in the respective DB. Then have the stored sort as a custom sort order based on the SortField value list in the respective DB. But since it is a global field, it cannot be indexed, and the value list doesn't work. Am i missing something stupid? I can think of a workaround, but i'd rather not implement it.

3.) If I am doing this the simplest way, one of the main issues is layout concerns. I could have a different layout for each database, and only have a specified number of criteria fields on that layout. In this sample solution, i would have a layout for customers which includes 9 criteria field, and a layout for Items which would include only the first 4 Criteria field. This would be an easy solution if there are only a few db's that need reporting features. The solution i'd like to implement this into will be around 20 - 25 DB's that will need reporting features. The other way i thought of to do this on one layout is to go ahead and have all the criteria fields on one layout with a button over each one that checks to see if the Tag field is empty. If it is empty, it doesn't allow entry into the field.

Honestly, even in the solution i want this to implement into, i don't think there will be a need for more than 10 Criteria fields in any given DB. If i was implementing this using the sample i provided, i probably wouldn't even allow criteria on the Addr1 or Addr2 Fields, etc. If the user needed a report from any fields not tagged as a criteria field, they'd have to go through a normal search feature. Also, i have both the Page Setup and Print Dialogs to show because i've learned that's what my users prefer.

Well, that's it. Please, if this is totally the long way to do this let me know (you can even make fun of me if it's that bad). I would like this solution i'm working on to be the final build so the more help i get on these type *extra* features the better. Everything is mostly organized, but i do not have comments anywhere throughout the solution, so if you have a question, just ask.

Thanks In Advance

Jeremy

REPORT TESTING.zip