need relationship help with many files

[wplate]

I have had FM since v.3, but have never had to do anything comlex, I'm in the midst of my most complex database yet, and I've run up against the limit to my understanding.

For a long time I could not understand Portals. The Invoice With Portals tutorial on http://www.maclane.com/ was a tremendous help, and got me started.

Based on its recommendations, I have an invoicing/order management system set up with a Customers File, an Inventory File, an Invoice File and a Line Item File. This is all great, and I love the way the files all communicate. Next I added a Registrations File to store the data we retrieve when people register our software. I had no problem tying the Registrations File to the Line Item file, they talked via an assigned Serial Number. For the most part, the system works fine for orders I will manage locally-- those sales I do to resellers, or to retail customers who we deal iwth directly.

But very often we don't directly handle sales. They can come from our web site. Data from web site sales comes in a report file that I will import into the Web Sales File. These sales have Serial Numbers assigned, but they won't generate records in my Customer File, Invoice File or my Line Item File. Somehow I need the Registrations File to be able to talk to the Web Sales file, since ALL registrations will go into the one Registrations File.

I've drawn it out on big sheets of paper, but I'm at a loss.

Here's what I made to try to explain this question to this forum...

http://www.wesplate.com/filemaker/

Any suggestions will be gratefully accepted. Thank you!

[Garry Claridge]

Wes,

You will need to create a relationship between the 'line item' file and the 'invoices' file (you will already have one the other way), based on 'invoice id'. You will now be able to see the 'customer id', in the line item' file from the 'invoices' file. Now create a calculated field in the 'line items' file which points to the 'customer id'.

The 'registrations' file should now be related to the 'line item' file by 'serial number'. You will be able to see the 'customer id' from the 'line item' file. You can now create a calculated field which points to the 'customer id'.

Now you will be able to relate the 'registrations' file to the 'customers' file and you should be able to see the customer name.

This basically create a relationship trail from the 'customers' file through to the 'registrations' file.

Good to see another Avid-L person here!

Hope this helps.

Garry Claridge

[wplate]

Hi Garry.

Thanks for your reply. I think I understand what you're saying, I'll look into it.

But on the problem with the web site sales integration, I'm beginning to think that a separate Serial Number file could be useful. When I enter a local number, the Line Item file copies its entries into the SN file. Likewise, web sales report imports would also copy thier SN and other relevant info into the SN file. That way the Registrations file could get everything it needed from the SN file.

If this is a good idea, the remaining hurdle is how do I get the Line Item and Web Sales files to automatically push their data into the SN file? Lookups strike me as fields that PULL data in from another field, I think I'd like to PUSH the data from the two sales files TO the SN file.

thanks again, Garry, it is always fun to run into other -Lers. Send in your photo for the gallery!! -)

[Garry Claridge]

Wes,

You can convert the 'registrations' file to a 'serial number' file. This file could store a registration date, and name, which would then indicate if a registration has been made for that serial number.

You can still pass the 'customer id' (and name) along through 'invoices', 'line items' to registrations/serial numbers. If a 'customer number' does not exist for that 'serial number' then it is a 'web sale'.

I may have a chance later today to have a closer look at this. I guess this is for 'Automatic Duck'.

All the best. (I've been meaning to send that photo for a while now ;-)

Garry