Copy data between unrelated tables/select from dialog window

[CarlS]

Hello!

 

I'm a software developer who is used to programming with languages such as C, C++, C#, Perl, PHP, etc. A client asked me to help him with a clients/sales management solution for his company based on FileMaker. I'm experienced with relational databases and had no problems setting up the necessary database for him. Creating simple Layouts to populate tables such as customers, products, invoices which are directly related is not a problem. I do have problems with copying data between unrelated tables though. Say I have the following DB tables (FK stands for foreign key, PK for primary key; table names are surrounded by hash signs: '#').

 

# Customers #

ID (PK)

Name 

Address

...

 

# Invoices #

ID (PK)

CustomerID (FK to CustomersID)

Date

Customer

...

 

# InvoiceItems #

ID (PK)

InvoiceID (FK to Invoice.ID)

ProductID (FK to Products.ID)

Name

Price

Packaging

Description

...

 

# Products #

ID (PK)

Name

Description

...

 

# ProductVariations #

ID (PK)

ProductID (FK to Products.ID)

Price

Packaging

 

What I would like to do is, have a layout where I can edit an invoice. There I would like to input the invoice information (thus populate the # Invoices # table) as well as input the items of that invoice (add entries into the # InvoiceItems # table). I already achieved these steps with the help of a portal (layout based on # Invoices #, portal based on # InvoiceItems #). The tricky part for me is to be able to copy vales from the # Products #, # ProductVariations # table into the InvoiceItems table respectively. I would like to have a button which would trigger the following behaviour (can either be in every portal row, or outside of it):

* Open a selection window (preferably a dialog) with all products, such that the user can select one of them (Name, Description fields)

* Once a product is selected, she has to have the possibility to select a variation of this product (Price, Packaging fields)

* Copy these "gathered" fields into either a new, or the current invoice item.

Thus:

InvoiceItem.Name = Products.Name

InvoiceItem.Price = ProductVariations.Price

InvoiceItem.Packaging = ProductVariations.Packaging

InvoiceItem.Description = Products.Description

 

Is this possible to achieve in FM in the way I would like to have it? If not, what other possibilities do I have to achieve the same effect.

A related question is: can I have a selection dialog (like the one described above) and, once the user selects one item, copy parts of it's data into a field in an unrelated table? (e.g. if I had an additional Table named SpecialProducts e.g., which has a differing structure from my Products table, but still has the fields Name and Description in it, which I could take over).

 

Thank you in advance - any help is greatly appreciated!!

[comment]

First, your InvoiceItems table is missing a ProductVariationID field. The ProductID field in the same table is optional and not required for your core relationships:

Customers -< Invoices -< InvoiceItems >- ProductVariations >- Products

Leaving the method of selection aside for the moment, you can see from the above that once the InvoiceItems::ProductVariationID field is populated, the item record will have one related record in ProductVariations and, by extension, one related record in Products. Therefore, this is not a question of copying data between unrelated tables.

The typical method to copy the data you describe is by performing a lookup. You can see the general idea in a basic invoice demo posted here:

http://fmforums.com/forum/showpost.php?post/309136/