Temporary table?

[jjfcpa]

I am fairly new to Filemaker, but not new to programming. I have an solution that generates a report that can be of varying length depending on the data that is entered. The records for the table that will be used to generate the report will need to be reused because the data used to generate the table will probably change often.

I have a couple of questions.

1. I am assuming that I need a table (occurrence) in order to generate the report?

2. What do I do with the table after the report is generated since I will no longer need to keep retain the data in the table? Do I delete all the records or delete the table?

3. Should I be compacting the database in order to alleviate bloat? Keep in mind that the temporary table will be created over and over again, but it is not necessary to retain the table data.

[Vaughan]

Your questions indicate that you're lacking in relational database design knowledge.

Tables and table occurrences are database structure. Tables and fields should not be created as part of the normal database operation: in fact there are no user-level scripts or commands that enable this. Re-think the solution to instead create records instead of tables and fields. The data structure should not need to change.

Compacting the database is not necessary. It can be considered a part of regular maintenance but it's not something that needs be done often by the user. There is no scripted or automated way to compress files, and the option to compress isn't available when the files are hosted on FMS, which is the nirvana of all FMP databases.

With respect I think you need to read up on relational database theory. Work through the process of creating an appropriate data structure for the task at hand. Once that is done the reporting will be a snap. If generating the reports is difficult then the data structure is not correct.

[bruceR]

Your questions indicate that you're lacking in relational database design knowledge.

Tables and table occurrences are database structure. Tables and fields should not be created as part of the normal database operation: in fact there are no user-level scripts or commands that enable this. Re-think the solution to instead create records instead of tables and fields. The data structure should not need to change.

Compacting the database is not necessary. It can be considered a part of regular maintenance but it's not something that needs be done often by the user. There is no scripted or automated way to compress files, and the option to compress isn't available when the files are hosted on FMS, which is the nirvana of all FMP databases.

With respect I think you need to read up on relational database theory. Work through the process of creating an appropriate data structure for the task at hand. Once that is done the reporting will be a snap. If generating the reports is difficult then the data structure is not correct.

Well, we need to know more about the original data structure that does exist. The OP assumes they need to create a report table; but it is far from clear that that is a valid conclusion. That said, in other db systems, particularly SQL, add/alter/drop table are valid and useful features.

There is a technique that I have done some work to popularize called "Virtual Lists" that provides for a great deal of reporting flexibility. In this technique, you create the table and record set once, and then use collector scripts to populate variables and the VL table displays the contents of the variables. So you create/update/clear the *variable*. There is no need to create or delete records and the technique is multi-user safe. It is used by Seedcode, by FM Search Results, and by a number of other developers. It is a very versatile technique.

However, I'll repeat that in the present case we need to know more about the existing data structure and it is not at all clear that the requested temporary table is really required. We also know that a collector script can generate HTML that can be displayed in a web viewer.

virtualList.zip

Edited July 17, 2010 by Guest

[jjfcpa]

Sorry for my lack of specificity, but I'll try to be more clear with an example.

Think of a solution that calculates amortization data. For example, you enter the interest rate, principal amount, term of the loan, and payment amount. Now you want to generate an amortization schedule by month. If this is a mortgage with a 30 year term, then you need to print an amortization schedule with 360 payments.

If this solution maintains multiple amortizations for hundreds of clients, you probably want to generate the table with the payments "on the fly" when they elect to print it instead of maintaining it. The reason for this is if they change the details of the amortization, then the information in the payments file is obsolete and you'd have to continually check to see if it is up-to-date.

So the question is, is there anything wrong with continually re-populating a table with payment information just so you can print the amortization schedule?

The best I've been able to come up with is a payments table that has may 60 X 12 (60 years X 12 monthly payments) records that are re-used so that you don't have to continually delete and add records when you are going to print an amortization schedule.

The payment's table is relatively simple:

Payment Number

Payment Date

Payment Amount

Interest

Principal

Check Number

Short Note

Let me know if I need to be even more specific.

[bcooney]

I see your need for a temp table. I don't think there is an advantage to editing existing records vs creating new records re:speed. What if two users are generating an amortization report at the same time? It's best that they both have their own set of report records in the payment's table.

[Lee Smith]

Take a look at this site.

Amortization

[bruceR]

I see your need for a temp table. I don't think there is an advantage to editing existing records vs creating new records re:speed. What if two users are generating an amortization report at the same time? It's best that they both have their own set of report records in the payment's table.

That's where the Virtual list technique comes in; as mentioned. It is multi-user safe.