How do I create my tables in FM7?

[Rochelle_505]

I recently was having trouble with the relationships in my tables. In fm6 all the tables were separate, now in fm7 they can all be in one file. (I am told this is why the relationships are not working correctly)OK so how do I move all of my tables into one file? The purpose of the 8 tables are to create an invoice from data entry. I have layouts in my Line Item table, will I have to recreate everything from scratch? I did try to add a table to test, but after I created the table and fields I can't figure out how to look at the table I just created to add records? I can only see the table that I created this 2nd table in.And which table should I use as the primary Invoice or Line item? Someone please help a confused new FM7 user. Thank you!!!!

[SlimJim]

Did you allow FMP7 to convert the files in your fmp6 solution? If you did then did the converted database work as normal with the separate files? It sounds as if you may have had some of the relationships go wrong. If it worked Ok after conversion then you are in good shape because you can now decide whether to leave it as it is in separate files or whether to make the effort of amalgamating it into one file. There is no law that says you have to put all your tables in one file and you can take advantage of the extra goodies in FMP7 without amalgamating your files.

With regard to putting all tables into one file there is no native solution for turning a collection of files into one file with a collection of tables. You either recreate the tables by hand or you purchase a program to do it for you.

Regarding your question of how do you find the records when you create a new table in FMP7 - look on the layout list and you should find a layout with the same name as your table. This is where the records can be found. Also look at the relationship diagram and you will find a copy (or ocurrence) of it in the diagram.

However, there is a major shift between FMP7 and earlier versions and you need to get your head round it or you will be forever puzzled.

Layouts are attached to Table Ocurrences (TOs) not to tables. I strongly advise you to at least skim through some of the Migration literature supplied online by Filemaker Inc.

[Rochelle_505]

Thanks so much for the information. I am going to do a test with all the tables in one file, and if that solves the relationship problem then I will continue in that direction.In the future if one table gets damaged will I still be able to open the file?

[SlimJim]

Sorry I don't know the answer to that maybe someone else does.

[Ender]

I recently was having trouble with the relationships in my tables. In fm6 all the tables were separate, now in fm7 they can all be in one file. (I am told this is why the relationships are not working correctly)

As SlimJim says, tables need not exist all in the same file. The relationships behave the same for local tables and external tables. You can reference tables by adding file references to those external files (File->Define->File References.)

If you want a solution to your relationship problem, I imagine it will be faster to ask about that than to rebuild your entire database only to find the same problem.

In the future if one table gets damaged will I still be able to open the file?

It depends on the extent of the damage. You would probably be able to open the file, but the data in the damaged table could be lost (one good reason for regular backups.) If there IS corruption, then the best practice is to import the recovered data into a clone of a known-good backup (another good reason for regular backups.) This could mean lots of work if there's lots of tables to import.

[Rochelle_505]

Before I rebuild the whole database, can someone take a look at my tables to see why my relationships are not working.

Line Item:Supplier layout

2 fields are not working, the description and the product #. And when I enter in a product # on the invoice table the description should also come up automatically from the completeliqourlist table, but it does nothing. These are the 2 fields driving my crazy. I am about to start all over, I included a word document to show exactly what I mean along with my table clones. PLEASE HELP!!!

CLONES.zip

Invoice_Screenshot.zip

[SlimJim]

I have had a look at your files. I have two comments. On the file that you uploaded there is no data in the CompleteLiquorList table.To be accurate there is one record with a supplier ID and nothing else. I put some data into the product ID# and Description fields and they show up OK in the Line-Item layout.

The second comment is that you say you want to put in a product ID# and have the description show up.Well the relationship between Line Item and complete liquor list is by Supplier ID not product ID so what you expect is that when you put in a supplier ID the first related product and description will show up.

One plea - when you upload a set of clones please move them into a new folder and rename them to the original filenames before you zip them up

[Fenton]

The "Product#" field in that Invoice portal is NOT even from the LineItems file. It is from the CompleteLiquorList2 Converted.fp7 file. The Description field lookup, in LineItems is based on a relationship using the InventoryID#; which works. (Also, I don't see why you'd have to choose an Inventory# AND a Product#.)

You cannot enter data into a portal field of another table that is not even related logically.

You need to sort out why you have a description in both Inventory and CompleteLiquorList2; which one or both you need, and where. I would think that description need not even be in the LineItem, unless you're editing it afterwards, as it could just be a related field. (I don't like unnecessary redundant data.)

You certainly don't need fields about the Supplier in LineItems, other than their ID. That's why you have a Suppliers file. And I would not put the picture for each LineItem. It should be a related field. You will be bloating that file considerably by unnecessarily adding the pictures.

I don't see any real reason for the Cost Reports table. It is the same data as the LineItems table?

You also should get FileMaker Developer, so you can rename your files correctly. Or get FM Robot and build all of this into just 1 file. Or start over and do so. I really think line items table should be in the same file as their parent. It simplifies scripting.

You should not, in my opinion, be using files with "Copy" or "Converted" in the name. It is confusing, and makes it awkward for us to even open the files, as we don't know what the real name is (though in this case " Clone" was all that was extra).

I don't like being so critical, but that's what happens when others analyze your files.

[Rochelle_505]

I am trying to fix this database that was kind of thrown at me. I think the best thing to do is start all over. Any advice on how I should structure the relationships for a simple invoice?

Again thank you all for the advice, I will use it wisely. :o

[Fenton]

Yes, I think I'd start over in 7, with all the tables in one file. For one thing it will simplify accounts & security (though accounts can be scripted). But mostly because all of these tables interact with each other. It is much simpler to script a process that moves between tables if they are in the same file.

This is an old solution. Looking in the File References (quite a mess) shows that it was first built in FileMaker 4.1, and was originally in the Program Files folder; very poor references; it will run like crap.

The logic of structure seems fairly sound (as far as it goes), as far as the basic relationships go. It is not that big a solution, so it wouldn't take all that long to redo. You could get FM Robot ($200) if you don't want to type all the fields again (though you really need to go through them first).

There are hardly any scripts in the whole solution (and they're not much good; copy/paste routines).

I can't really see how it would have worked very well. There just doesn't seem to be much integration between the files. For example: there's an Inventory file, with a Quantity field; but there is no process to do anything with it, or even show how many were sold. Stock levels is a fairly serious operation, but why have such a field if there's not even a relationship to the LineItems?

There is also some unnecessary redundancy, and some confusion about it. The Customer is in the Invoice twice, once as regular fields (but not even lookups), and once as related fields (not needed in any case). What does that mean?

There are Inventory Items fields (cost, etc.) in Invoices, repeating fields AND also LineItems. The repeating fields are left over junk from way earlier, but never deleted.

As I said, Cost Report seems unneeded; unless you've got more going on.

[Rochelle_505]

I am in the process of cleaning up the tables now. I am getting rid of alot of the redundant/old fields. I think I may need help with the relationships later. I will post my DB if I run into any problems when I am finished with the new tables. THANK YOU VERY MUCH!!! :o

[Rochelle_505]

Preview your topic or reply

I have recreated my tables all in one file. I need some help with my relationships and lookups. Some fields are not showing up where they are suppposed to. The portal in the invoice table is not working, it is not showing the line items at all. So before I really mess this up can someone please help!!! In the old DB there were a few calculation fields called All, does anyone know if I still need to use this field? What does it do? I have added a word doc to visually show what is missing in the report. The layout with the missing records is called layout #11. Thank you!

My zip file exceeds the limit!! If someone can provide their e-mail address I can send it via YOUSENDIT, where they e-mail you a download link to my files without hitting your inbox with a huge file. Please help!!!

[-Queue-]

Did you create a clone of the file before zipping it? We do not need the data, just the file structure.