Using repetitions

[Jason Vearncombe]

Hi i am having problems using repetitions, i have a field that you enter a product code in to and the next field looks up from another table but i can only get it to work on the first repetition.

Any ideas what im doing wrong ??

Edited July 13, 2008 by Guest

[LaRetta]

Hi Jason!! Welcome to FM Forums!!

Any ideas what im doing wrong ??

What you are doing wrong is that you are using repetitions for standard data. You really need to change your structure to using relationships. If you switch now, it will be simpler ... later, after your solution has grown, it will be very difficult to change.

It is the most common error of people new to relational theory and FileMaker. We will be happy to help move you to a proper structure.

LaRetta :wink2:

[Jason Vearncombe]

Do i need to make a field for every item that could be added then ?

[LaRetta]

No, not a field for each ... a record for each, in a related table. If you can post your file, I can make specific suggestions and even give you a basic relational approach from which to work. :smile2:

[Jason Vearncombe]

Cool, have a look..

Test_App.zip

[LaRetta]

Here are some basics and you will see how I changed your tables accordingly. I also added a script to show you how you can add detail lines to your order. Feel free to ask questions but understanding this basic setup will take you quite a ways into good basic structure.

1) Always create an ID for every table which is an auto-enter serial. Never allow this field to be changed (stop it from being modified by disallowing field entry in browse mode (from field behavior). Always join your relationships using these unique FM-generated serials.

2) Always break things into their finite parts, ie, you have ContactName with both first and last names. Split the name into FirstName and LastName. Also, remove the spaces from your field names because, if you ever want to web publish or use with SQL, spaces are bad. Use underlines if you wish.

3) You have one Customer with many orders. So you will put the CustomerID also in the Order table. In this way, Customers is now related to their own orders. You can now place ANY customer field directly onto an order layout to see the customer information.

4) You have many details (line items) for each Order. So you will put the OrderID also in the Order Details table. In this way, Orders is now related to their details. You can place any field from Orders directly onto an Order Detail layout and view the Order information AND ... AND ... you can place CUSTOMER fields also on the Detail layout. You see where this is going? View the Orders Details - there are fields from Contacts and Products there. In layout mode, see the : at the beginning of the field? This indicates it is from a related table and not the same one the layout is based upon (which is Order Details).

As you move through your relationships, you won't have to duplicate information, instead you just cross-place fields because FileMaker knows they are related.

So Orders has many detail lines. Each Detail line is assigned a ProductID. You look up the Product ID from the Products table and insert the ProductID into the Details table as well. Then the COST will automatically pull in and the Product Description ... because it is related, can simply be displayed in your Detail table by placing the Product Description directly onto the Details layout.

I whipped this together very quickly (see attached) but I think you will get the overall understanding of how, when you have many items relating to one, such as many Details relating to one Order or many orders relating to one Contact ... that you should use relationships and records instead of repeating fields or multiple fields within the same table.

Please feel free to ask questions here ... FM Forums is the best resource in the universe.

LaRetta

Test_AppMOD.zip

[Jason Vearncombe]

Thats great, thanks alot. I can see exactly where i was going wrong now.