Relationship IDs getting screwy, why?

[spankalee]

In my Invoice system addresses are stored in a separate Address table. The three addresses associated with an invoice are referenced by address ID. The relationship allows the creation of addresses from invoices.

When you start entering an address FM creates a new address record, gives it an ID, and assigns the ID to the AddressID field in invoice. Nice.

The problem that's cropping up is that sometimes the AddressIDs are getting messed up. A lot of them are just being set to "1", other times it's set to some other random ID that's being used by another invoice. That causes a lot of trouble because changing that address will change the address on another invoice.

I can't figure out what's going on. It seems like FM does create a new Address record sometimes, and then switches addressIDs on the invoice and orphans the record.

Am I doing something wrong here? I need to fix the problem whether or not it's FM's fault or mine.

[spankalee]

Man, FileMaker is really pissing me off now.

So about every other invoice that's entered gets this problem where an error dialog comes up and says: "AddressID is defined as requiring a value. Would you like to revert the entire record" (or something close to that)

I've put the AddressID fields up on the layout so I can look at them and fix things when they break. The invoice has good addressIDs, but FM is still complaining.

Finally FM let me finish the record. Then I go and look at the address table and there are duplicates of the addressIDs for that invoice. I have no idea how duplicates were created. They have the same ID, same data, same everything. The IDs are defined as being unique also.

What's going on here???

[Ender]

If the address for an invoice is created by the invoice, then this implies that the relationship between Invoice and Address is Invoice::InvoiceID = Address::InvoiceID. So what is the Address ID in the invoice table used for?

Maybe you can be more specific about the relationships involved. Or is the address creation done by a script?

[spankalee]

Sorry Ender, I should have been more clear.

Each address record has AddressID, and each invoice has three relationships to addresses:

Invoices::MainAddressID = Addresses::AddressID

Invoices::BillingAddressID = Addresses::AddressID

Invoices::ShippingAddressID = Addresses:AddressID

There are three separate table occurrences of the Address table.

[Ugo DI LUCA]

Hi,

This wouldn't directly solve your issue, but...on a logical point of view...

I'm unsure why you established a link from an Invoice to an AdressID, either using the AdressID or the InvoiceID.

Having your Adresses stored in another table makes sense, but you should still use the CustomerID and then choose from there which adress you want to display still based though a relationship CustomerID::CustomerID to the Adress Table.

Then pick up your respective adress either through a SetField[ ] or a lookup, using either a portal or a value list.

IMO, a lookup would be prefered to a simple related field, so that to avoid modification to this record after it has been invoiced.

[Ender]

So how are you creating new addresses?

It seems the only ways this would work would be: A. Go to a layout based on Address and complete a new address record, then go back to the invoice and enter the new Address ID in the appropriate field, or B. Enter the new address in a set of globals, hit a button to copy the contents of the globals to a new Address record, then set the appropriate Address ID in the invoice to the ID of the new Address record.

But either way, I think the Address record creation has to be done in the Address table, not through a relationship from Invoice.

[Kurt Knippel]

Also with FM7 you need to make liberal use of the Commit Record script step. It is possible that you address records are not being comitted in time to be added to the invoice. Basically use the Comit Record step everytime you use a set of Set Field commands within a script.

[Ender]

Ugo's point is good. If you start the process from a Customer (which could have multiple related addresses,) then create an invoice, when on the invoice the user can select from just that Customer's addresses.

In this case addresses could be entered for a Customer through a simple portal (Customer::CustomerID = Address::CustomerID).

[spankalee]

So how are you creating new addresses?

It seems the only ways this would work would be: A. Go to a layout based on Address and complete a new address record, then go back to the invoice and enter the new Address ID in the appropriate field, or B. Enter the new address in a set of globals, hit a button to copy the contents of the globals to a new Address record, then set the appropriate Address ID in the invoice to the ID of the new Address record.

But either way, I think the Address record creation has to be done in the Address table, not through a relationship from Invoice.

FM creates the Address record automatically as soon as I enter any data into the fields in the Invoice layout. I've always assumed that this is the way to do it since the relationship dialog has "allow creation of records in this table via this relationship"

I thought it was quite a nice feature actually that FM would create the Address record and assign the ID in in the Invoice record.

[spankalee]

I'm unsure why you established a link from an Invoice to an AdressID, either using the AdressID or the InvoiceID.

Having your Adresses stored in another table makes sense, but you should still use the CustomerID and then choose from there which adress you want to display still based though a relationship CustomerID::CustomerID to the Adress Table.

The addresses are not always pulled from the Customer table. Customers for us are usually stores, but often times we are drop shipping to one of their clients, or billing a third party. So I've never though of the addresses as being related to Customers (Though customers do have their own addresses in the same table).

[spankalee]

I whipped up a little example to show how the addresses are created. It's all done from the Test layout in this file.

Test.fp7.zip

[Ugo DI LUCA]

I thought it was quite a nice feature actually that FM would create the Address record and assign the ID in in the Invoice record.

Nice ?

Even if it is possible, you'd rather think about it as relationship flaws.

There is no direct relationship (strictly talking) between an Invoice and an Adress, while there is one (unique moreover) from an Invoice to a Customer, and obviously as many as you wish from one Customer to some Adresses.

[Ugo DI LUCA]

Forget part of my previous comments as I was posting while you were answering.

I take a look to your file now

[spankalee]

What if the address has nothing to do with the customer as in the case of a drop ship?

I guess I see it a little differently, that there is a direct relationship between the invoice and the address. Many of our customers will place multiple orders a day that are all going to different addresses.

Edit: No problem, Ugo

[Ugo DI LUCA]

Customers for us are usually stores, but often times we are drop shipping to one of their clients, or billing a third party.

Then these would now be part of your customer/contact table, either for the Shipping order or the Invoice.

So the adresses are related to your Customer/Contact IMO

[Ugo DI LUCA]

That's good. Pretty dynamic thread

Do you separate Invoice from Shipments ? Where Invoices are the Sum of Shipments made ?

[spankalee]

Then these would now be part of your customer/contact table, either for the Shipping order or the Invoice.

So the adresses are related to your Customer/Contact IMO

Hmm, interesting. I'm not quite sure how you would implement that idea though.

[spankalee]

That's good. Pretty dynamic thread

Do you separate Invoice from Shipments ? Where Invoices are the Sum of Shipments made ?

No, at this company every invoice is a shipment and vice versa. Most of our invoices are forwarded by our customer directly to their client to be paid.

[Ugo DI LUCA]

Ok,

I have these kinds of procedures in my files too, as we daily ship goods to our Customer's client place.

These clients are stored in my Contact File, with a recursive relationship to the Customer.

Say my client is Company A and ask to deliver goods to his client place Mr B

Mr B is entered as a Contact and related to Company A.

All adresses are stored and related the uniqueID.

When preparing a shipping sheet or an Invoice, I have this option to select either the CustomerID, or/and the subset of related Contacts for this Customer.

HTH

[Ender]

spankalee,

I see from your attachment what you had in mind. Although it does appear to work (at the moment), I don't think it's a very clean way to do record creation. The relationship between Invoice and Address doesn't exist until both Invoice and Address have the Address ID populated, but you are putting in data into the related Address before that relationship exists. It has to create an Address record, generate an ID, use that ID to populate the Address ID in Invoice, then put the data that was being typed, into the related address field.

Usually the key exists in the parent database when a related record is created, so it knows how to populate the match field key when the record is created.

I suspect that this may be what's causing your intermittent missing key problems. Maybe over a network it's too slow for the key to get pushed back and forth in the time that someone is typing and clicking around.

I've never actually seen record creation done backwards through the relationship (from child to parent--in this case Invoice is a child to Address), so maybe I'm wrong. Anyone else used this method?

[spankalee]

In that case Ender, it's strange that it works at all. I admit that I kinda stumbled onto this functionality... so maybe it's not quite a "feature" yet.

Like I said, I think it's rather nice to let FM handle creating the Address record and linking the Invoice. To me it seems clean and the ideal FileMaker way to do things (simple and easy, right?)

[spankalee]

So I've sent some of my files to FileMaker for them to check out, but I haven't heard back from them yet.

Let's assume that the auto-creation and linking of Address records is not a good "feature" to use, especially over a network. How should I solve this problem? What's the usual way of handling this?

My first though is to disable New Record, and have a "New Invoice" script that sets up the Address records. Then I'll be able to do whatever tricks I need to (Commit Record, etc...) to make sure that everything links up fine (Though I'm not sure what I'll need to do).

I'd like to not change the schema and relationships for now.

Any suggestions?

Thanks guys,

J