relationships and portals

[mediarare]

So, I've thought it weird how filemaker did relationships for a heavily normalized database since fm 5. Now I'm using FM7, and my client who uses the database as a bound solution is having troubles with line items switching invoices they belong to.

I've gone through and added commit records to everyplace I can think where somethign get's changed, and yet they are still having line items switch to other invoices.

My next step is to go through and remove all lookups but I'm having trouble with some lookups apparently being neccsary in some situations, so here's what I've got, maybe someone can propose a better way of doing it. This is an inherited application that I did not write, so there are many things I do not agree with in it's design.

There are seperate files for artists, items, clients, staff, invoices, line items and payments.

The artists file just stores info about the artist

The items file just stores info about the item, and what artist made it

The clients file just stores info about clients

The staff just stores info about staff

The invoices file stores info about who sold what to whom, how much it cost and when it was sold

The line items stores what item was sold on which invoice and how much it cost

The payments file stores payments made, and what staff member log'd the payment and what invoice the payment belongs to

There is a need to the clients contact information in the invoice incase they update it later or something.

The line items file needs to store more than just an invoice id, and an item id as a layout in the invoice file contains a portal that shows the items sale value, name and which artist made it

The payment file is fine as it just stores a referance to what staff menber log'd the file.

Now as far as any primary keys, I've stopped using auto enter serial ID's infavor of the Unique Auto-entered Primary Key IDs v2.1 as linked to me by captkurt at http://www.fmforums.com/threads/showflat.php?Cat=0&Number=100135&page=0&vc=1#Post100135

So I'm wondering, are lookup values what I really want for storing a copy of the client's address in the invoice file, or sould I be using like auto enter calculated values. It is a thing that for some unforseen reason the client for an invoice might be changed shortly after the invoice was created if the staff member chose the wrong client or something. How would an auto enter calculation value update itself if the client id was changed etc.

My ultimate goal is to get line items to stay with the invoice they where origionally associated with, as well as payments made toward the invoices. My client is reporting both payments and line items moving to different invoices and I'm not finding any obvious reasons for this behaviour to occur.

Thanks guys!

[mediarare]

if this helps anyone, The weirdness is happening with a bound solution on mac OSX in filemaker 7.

However, this is a cross platform solution, so running on mac or windows is very possible, though it will be a bound solution.

[Kurt Knippel]

I would start by adding some auditing fields to the tables so that you can track who is changing what and when.

It would seem that the lookups are the cause...but lookups would not typically be used to set the invoiceID foreign key in the line items file.

Hmm...so a bound solution is having problems only on OS X? What happens if you run this solution in a full copy of Filemaker Pro on OS X?

It could very well be some bug in the runtime engine for OS X.

[mediarare]

Thanks captkurt for the reply.

The lookups are used mostly for data that's used in the report or for getting at data in a related related field 2 tables away like. The fields I'm usin as the foriegn keys are just text type, with no options on them.

I just do not see any scripts that mess with the foreign keys and they seem to reset when adding new records. the way new reccords are added are by a script in the file where the record is Kept, so to add a payment to an invoice, a button on a layout in the file responsible for the invoice calls a script in the payments file that calls a new record request, then copies the values of globals from the invoice file that where set before the current script was called, and then commits the record and hides the payments file leaving the focus back on the inventory file.

I havn't had the opportunity to test it unbound, but that may be the next step that I try.

Should I be leary about a bound solution? I know that the instant webpublishing works mostly for Internet explorer and that filemaker's own documentation deep in a white paper in fine print recomends not to use the instant web publishing and to write your own. Just want to be sure that bound solutions arn't similar in that they work, but are a quick fix and should not be relied upon.

[mediarare]

I should add the randomness of which this foriegn key movement occurs. it's such that not every new record will make the foreign keys shift, it's some times every other, and others every third. And this behaviour makes me think it's not with my scripts, and that it's either that it's a bound solution, or user error.