Quicken Substitute

[Ron K]

I'm trying to produce a Filemaker version of a home budget program with similar functions of Quicken. I started out with Filemaker's "Check Tracker" template, and added additional features. I'm having some trouble implementing the "Split Transaction" feature. This feature allows a record to have sub records. As an example, if the record was a credit card bill, the split transaction allows typing in all the individual purchases made by that credit card for that particular bill.

I could create a relational database file for each split, but that seems like alot of files to keep track of as the database grows. Is there another way to do this?

Thanks,

Ron K

[Fenton]

No, not "files." You could create a separate Table for the transactions. It would mean that most of your entries would have a single entry in the main entry lines table, and a single record for each amount entered; but a few would have more than one record, for the "split" entries.

It seems a little redundant to have mostly 1-1 correlation, rarely 1-2+; but it gives you the functionality and flexibilty you may need. Because you could still track the split entries separately if desired.

If however you NEVER need to separately track the "splits", you could use a repeating field for each column of your repeating splits. Cheap trick, but works.

In either case you'd need 2 or more rows of it on your layout, or a way to switch to a layout with splits showing.

[Ron K]

Thanks for your suggestions.

The quirk with using a repeating field is that I would need to be able to automatically generate additional repeating fields for the active record, depending on the number of credit card purchases made for that record.

Quicken's "Split" window consists of "Category", "Memo", and "Amount" fields, as well as an "Amount Summary" at the end. This table keeps expanding as additional entries are made.

Perhaps I could create a new relational file(Table) using a primary Key ID based on the particular record. But that would be sooo "Access-like". YUK! -)

[Fenton]

The problem of space on the layout would be about the same with repeating fields or a separate table. The repeating fields can be defined with as many repetitions as you'd ever need, so that is not a problem.

I would do the "splits" as a "pop-up window", ie., a small window, scripted. It would have only the fields required, no toolbar, but buttons required. It would, on Windows, require you to drop out of Maximize however. It is possible to get the little window to pop up at the same level as the row you're on; but a bit tricky.

The real question of repeats vs. new table is whether you need to track the splits independently. I think I would. The separate table allows flexible reporting. It is not "Access-like" so much as "relational database."

Are you really using FileMaker 4, on Windows 7? That's kind of large gap between OS and FileMaker versions.

[Ron K]

Thanks Fenton,

Each approach has its advantages and disadvantages. I found a good post on repeating fields at:

Repeating Fields

A suggestion was to investigate portals, which I'll do.

Yes, Filemaker Pro 4.0 is pretty old, but that's what I've got. It seems to work well with Windows 7 on my 32bit machine. Not so good on my 64bit computer. -)

[Fenton]

Well, a straight relationship like this (with [x] Allow creation of related records (with [x] Delete related records also, likely), with a portal would work fine. But it does not solve the problem of how much space you're willing to devote to the portal.

An older technique, which still works, especially in conditions like yours; ie., a fairly limited interface, a list view, not all that many fields, etc., is to do your regular list view completely first. Then duplicate it, make the Body area taller with the multi-line portal in it. A script would go to that layout when you had splits. This technique still allows Maximize mode in Windows, if you prefer that.

[Ron K]

OK,

Here is how I plan to proceed. I've had good success with the initial prototype functions so far.

The basic application architecture consists of two tables. The first is a modification of the Filemaker "Check Tracker" Template. I've added a few things including a "Primary Key" field and a "Split" button. Clicking the "Split" button performs a script and opens the second table.

The second table consists of "Split" information. This consists of individual credit card transactions corresponding to a particular record in the Check Tracker table.

Each record in the Split table would have the primary key pasted from the corresponding record in the Check Tracker Table.

Using Scripts, I'll be able to find the particular split I need based on that primary key.

Hope this makes sense.

[Fenton]

Yes, that sounds right. I'm not sure why "using scripts" for Find is required, as a regular Find in the portal will work. It automatically respects the key of the relationship.