Kevin, what is the relationship between your Customers table and the Work Order table? Do you have a unique primary ID in all your tables? either a serial ID or UUID? and then a foreign key ID field for the customer in the Work Order table ? then you can relate Work Orders to Customers.
Thanks for the response!
No I sure don't, what do I need to do? I attached what my relationship folders look like in the previous post.
click on the box between Table 1 and Table 2 ... it will tell you how the two tables are related.
also you should change the name of your tables from Table 1, Table 2 to Customers, WorkOrders.... will help understanding the graph better. In the WorkOrders table (Table 2) create a new field ..type: number and call it CustomerID. Then change your connection between Customers (Table 1) and WorkOrders (Table 2) to be Customers::ID = WorkOrders::CustomerID.
Ok just getting back to this! I did everything you said to do! I created the ID's pretty much like you are showing and that works. What happens when I go to import my customer database? I am moving from Lotus Approach flat file to FM. When I do the import will it gerate the ID's for me?
I went ahead and changed my table names, but I may be missing something! For my WO'S I am getting Unrelated Table on my layout screen. The Database(Table) file name is WO-Main and my Table is called WO Main would that cause the problem? Just didn't want to try a bunch of stuff on my own without asking first and create a lot of work for myself.
Question on serial numbers on import: Serial numbers can be created on import, or dealt with later by doing a field replace. Bigger concern is matching data from your old file to new FM file ... Have you been able to export the data from the Lotus Approach file?
Question on unrelated table showing from fields on your layout: Check the Table Occurrence the layout is based on ... in Layout mode and to "Layout Setup".... note and correct where it says "Show records from". check that the fields on your layout are from that TO and that any related data (ie. portals) correspond to the related table name from your graph.
how much data do you have in your FM file? I am assuming just some test data... is that correct? Do you want to attach your file ? or send more screen shots. This would make it easier to understand and respond to issues you are describing and to better advise on best approach to get data from Lotus to FileMaker.
I have attached my files, granted this is nothing pretty so far since it was all in testing and trying to customize to my small business. I just had some test data in there.
The file from Lotus Approach to be imported is in .dbf which I have a few that will be added for customer contacts eventually. I want to be able to track customer, work orders, expenses, mileage, parts and do quoting and invoices etc.
The AWM-Main is the Main Database.
Thanks A Lot For The Help!
Good afternoon KevinRoach62,
I hope your day is going well. Although I'm not able to open files with the .fp7 extension, I thought these images might be helpful for you:
This one is how I would structure the relationships between your existing tables; you should be able to add the primary and foreign key fields without changing any of the others:
If you look closely at the connections, you'll notice they are one-to-many relationships - your's were many-to-many (a fork on both ends of the connection). You can accomplish the one-to-many relationship by configuring your primary key fields ( __pkID) this way:
The foreign key fields ( _fkTableNameID) can be configured as simple text fields. Then base all of you layouts - this rule can be broken if necessary - on the Customer table and use portals to display related data. I hope this information is useful to you. Have a great day!
I suggest you start by getting all the tables into one file.
I am taking this all in. How do I go about putting everything into one file?
When I started I was confused on if FM allowed Tables to be in one file or if it had to be seperate Tables(Databases) for the different data I was collecting under Custaomers.
Kevin, I'm assuming you want to do this yourself.... let's get you to a good starting point.
(1) First remove all the extra tables you created ...either move them into other folder or delete them... there is no data in them so no worries....keep the AWM-Main table and open it.
(2) go to <File> menu <Manage> <data sources> ... delete all the links to external sources that you created.
(3) Go to <File> <Manage> <Database....> <Relationships> tab and delete the WO Main TO ( grey with <data source missing> )... click on the Tables /Relationships button on bottom of screen ...left most button to create anew TO ...choose the WO Main ...name it "WorkOrders"... while you are at it rename AWM Main to "Customers"
(4) still in manage database ...click on the Fields tab ...in WO Main create a new primary field like you did in your main table (customers) . create another field CustomerID that will be a foreign key field that will allow to make a relationship from Customers to WorkOrders. make sure you make all these fields type : number
(5) go back to the Relationship tab and click on the ID field in Customers and drag over to the CustomerID field in WorkOrders.
(6) the other tables all need to have an primary ID field like you created in AWM Main (Customers) and a foreign key field of other table you would like to link to ...ie. if the EXPENSES table is related to a WordOrder..... ie. you want to relate multiple expenses to one Work Order create a WorkOrderID field in the Expenses Table.
Hope that gets you moving forward. You need to start thinking about processes....like, how do you create a new work order for a specific customer. etc....attached your file with changes if you want to reference.
AWM.fp7.zip 24.2 K
Kevin, check out the resources offered in this site. Go to Resources and there are some free download materials available that will get you up to speed designing your solution.
FileMaker Training Series: Basics
or the Quick Start Training : Learn
" Kevin, I'm assuming you want to do this yourself.... let's get you to a good starting point.
(1) First remove all the extra tables you created ...either move them into other folder or delete them... there is no data in them so no worries....keep the AWM-Main table and open it. "
Do I need to completely not use any of my ext. data sources? Also do I get rid of my layouts?
Thanks for the reply!
I am a little confused by _pkID(primary key) being in Customers and Workorders. Are they related or have the same value that is indexed? What do I need to do to set it? I know I have to create the field but is it indexed the same way.
Does _fkID(foreign key) have to be text or should it be a number? Not sure how in FM to select settings for the foreign key.
I understand the one to many relationship it's just getting it setup for the first time.
In addition to moving all the tables into a single file, I suggest you name each table descriptively—Customer, WorkOrder, Part, etc—instead of using generic terms like Table 1, Table 2, etc. This is all part of the process of developing the overall setup. For very good reason, you should start with your computer switched off, and work with a pencil and paper to draw up the various elements of the database you want to track. Some real basics before you switch on Filemaker will pay off in the long run.