Updating repeating field with data of another file

[Nearly there, but not quite]

Hello,

I think I am trying to be a bit too ambitious here but if any one can help I

really would appreciate it.

This is what I have been trying to do:

I have one file for storing invoices and generating a confirmation of an order

form and on the form is an area which shows what products have been selected by

the customer to purchase. This area has repeating fields in it so multiple order

items are shown.

i.e.

file1::ProductID || file1::ProductDescription || file1::ProductIDInstance ||

file1::ProductStatus

Now the the difficult part for me is that the customer wants to be able to fill

out this form dynamically by two means. First they want to be able to enter the

ID manualy by typing it in and secondly by selecting it from a drop down list.

Now at present my ProductID values reside in another file and by using a lookup

I have access to that data and I do show it as a drop down list when they enter

the field so they have that functionality as well as being able to enter the ID

by typing it in.

When they enter the ID it does cause my ProductDescription field to

automatically populate itself but the problem comes with the ProductStatus and

the ProductIDInstance.

The 'Products' Explained

-------------------

My products all have a unique ProductID to identify the product type and they

also have a ProductIDInstance which identifies the individual instance of that

product. Finally each individual product has one type of status attached to it.

So for example: ProductID = 12 ProductIDInstance = 34 ProductStatus = InStock.

Now obviously if the customer wants to select a product I need to first check

that there is one with an InStock status and if so automatically populate the

other fields. There could be more than one product instance InStock with

different ProductIDInstance numbers and I would need to select one of those.

When it has been selected and the Confirmation of Order form exited I will need

to change the InStock status to InStockAllocated.

Thats a straight forward find, but the customer has Products which have other

status's like OnDisplay, Damaged etc, which are still available to their clients

if they want them.

So they want the ability to first of all select the product and in the

ProductStatus field they want a drop down list showing all the status's for

that product so they can then select by product status and then this should

automatically change the ProductIDInstance number.

All my product instances reside in one table in the other file.

Can any one please help I have been playing around with this one for a while now

and being new to FM I am struggling with not so much the logic of it all, but

more with what tools in FM can do the job for me in the correct order.

Sorry about the length of this post, I just wanted to give as much info as

possible for people to be able to consider the problem fully.

Thankyou in advance and if you celebrate Xmas, merry xmas!

Bill

[bruceR]

You need to get rid of the repeating fields and use real related records for the order items.

[Barbecue]

I have to agree with BruceR.

Basically what you're doing now is storing invoice line items in a way that makes it very awkward and difficult to manipulate your data in a sophisticated way.

Repeating fields are really a holdover from FileMaker's early days, when it was basically a flat-file database. There are a few situations where they can still be useful, but in general they are not a good idea, and the project you describe is definitely not a good place to use them.

At a minimum, you should have three separate tables for the data you describe: A table to hold inventory stock items, one for invoices, and one for invoice line items.

Include the invoice number field in both the invoices table and the invoice line items table, with a relationship between the two based on that field. The Invoices table should contain only information relevant to the invoice as a whole, such as order date, customer, billing and shipping info, etc. The Invoice Line Items table should contain the details about each item, such as the product, price, quantity, etc.

You'll also need a relationship between the invoice line items table and the inventory items table. Presumably your inventory items have a stock number or catalog number associated with them, which you could use as the basis for this relationship.

Creating related tables like this will allow you to do the sort of things you want to do much more easily.

I suggest you download the Business Tracker solution, a free example of a FileMaker business database. It's fully editable, includes customer and inventory management features, and you can use it as a guide for editing your own database, or even create a system based on it.

http://collection.filemaker.com/collection/search_details.jsp?serial=2206