<Unrelated Table>

[halepic]

I have taken over creating an inventory database, keeping track of different types of inventory within the company. I have also been tasked to create a page that will show management what an employee has checked out. The first four tables work without a problem. The last table, shows "" in the browse mode.

The layout that I am having problems with is "inventory check out", this page pulls information from two different tables. First table is "main", the second is "history". I created a relationship to both the "main" & "history" tables in order to create a relationshp from the "Inventory Check Out" table to the "Equipment Checked Out" table. The "Equipment Checked Out" table works fine with the other four portals that I am using to display my report.

I believe that my main problem is naming conventions. The problem starts out with another table "Contacts" The person who started this project created a calculation field in the history table to create "Assigned To" from the "Contacts" table "Full Name" field. "Full Name" is a calculation from "First Name" & "Last Name" fields.

I hope that this can be fixed, I don't want to have to create a new table.

[LaRetta]

I find your description a bit confusing but that's only because we can't see it. It would help if you could provide a file so we can clearly see your relationships. It doesn't sound like you use IDs to bind your relationships, ie, you are using FullName. This is incorrect structure and should be changed immediately.

Can you post a sample file or post your real file, zipped first? You can remove all data by creating an empty clone (File > Save As). It would sure help us help you.

UPDATE: Besides the fact that FullName should not be used to bind a relationship, the only problem with it may simply be that it is not indexed (ie, in FullName field definitions and Store Options, it is not set to Index Minimal (at least).

DOUBLE UPDATE: Hello to Eugene, Oregon - my old (and endearing) home!!

LaRetta

Edited March 9, 2009 by Guest
Added Update then Double Update

[halepic]

Here is a copy of the database that I am working on. I had to remove a view of the tables to make it small enough to fit.

You can open the file using the "Guest" account.

The problem is: I created a table "InvCheckedOut" to create a relationship between "History" & "Main" tables.

I'm wondering that because the "Assigned To:" field that I'm using in the "History" table is a calculation from the "Contacts" table. It was created from "Contacts::FullName", which in turn was created from "Contacts" "FirstName" & "LastName".

I know that this is confusing, but I took the project over and don't really want to have to create a new layout "Inventory Checked Out" from the two different tables "Main" & "History".

Thanks again for any assistance.

InventoryDuplicate.fp7.zip

Edited March 10, 2009 by Guest

[LaRetta]

There are a few things …

You can’t base your relationship on Full_Name when it is a calculation which derives its value from another table (as you are finding out). Neither should you be using Full_Name or Assigned To: to hold a relationship together … you should ALWAYS use meaningless IDs. There is a ContactID in History and a Contacts table. I assume you realize this and I also realize you got stuck with this design so I’m mainly covering points just for clarity.

Instead of using Assigned To:, I suggest you place a ContactID in InvCheckedOut. I’m assuming ContactID and AssignedTo reference the same people? Without data, I’m a bit restricted in understanding your data interactions. Since ContactID is standard text field in History, it will work perfectly well because it can be indexed. Then, when you select the ContactID in InvCheckedOut (using pop-up value inserting ContactID but displaying Full_Name) you will complete the relationship. The value list for this would be based upon Contacts table, all values.

Can you explain more about these three (Employee, Contact and AssignedTo)? I am confused as to the difference between them. It seems that Contacts are AssignedTo. And that AssignedTo are Employees. ?? If so, you don't need an EmployeeAssignedTo table occurrence - you can just use your Contacts table. But I might be confusing meanings here.

BTW, remove the colon from Assigned To: field name - it is a reserved character. I also suggest removing the spaces between words or switching to underscore between them (although this can be done later). :wink2:

[halepic]

Thanks LaRetta, I'll work on this. I will also go over all of my Tables and Fields to make sure that the naming conventions are consistent. Also, I will try and explain how (Employee, Contact & Assigned To) interact. Thanks again for all of your help.

Edited March 11, 2009 by Guest

[halepic]

Hi LaRetta, I had added two screen shots. I am still not getting the relationship to work. I went in and changed all of the "Assigned To" to "Employee". The first screen shot shows that InvCheckedOut is not related to any other tables. The second screen shot shows the relationship that I created between "Main" & "InvCheckedOut", using the "AssetID" field. Any help would be most appreciated. I know it must all stem from the "Contacts" table and the "Employee" field that is a calculation marrying the "First Name" & "Last Name" fields. I hope I am being clear. Please let me know if I can provide any additional information. Thanks again, Hal

[LaRetta]

I know it must all stem from the "Contacts" table and the "Employee" field that is a calculation marrying the "First Name" & "Last Name" fields. I hope I am being clear.

You are not clear at all, sorry, and I spent quite a bit of time today trying to unscramble it all and with 13 other downloads and no other responses, I'm not alone.

The person who started this project created a calculation field in the history table to create "Assigned To" from the "Contacts" table "Full Name" field. "Full Name" is a calculation from "First Name" & "Last Name" fields.

I asked for clarification between three tables: Contacts, Assigned To and Employee. I now see that Assigned To is Employee, right? But it must also be Contacts then, since you mention the Contacts table and the Employee field (although there is NO field in Contacts called Employee). You should not use FullName in any relationship ... I keep saying this and I'm unsure if you understand. Is the ContactID the unique serial ID of the Employee? I have no data to work with so I can't even guess.

I took the project over and don't really want to have to create a new layout "Inventory Checked Out" from the two different tables "Main" & "History".

Why would you? What are you trying to accomplish? You haven't told me what you are trying to do - only how you've tried to do it. I don't know your purpose nor perspective. Your EmployeeCheckedOut table has the AssignedTo set as a global. Why? You must eliminate using FullName through this solution. You must switch to using the unique IDs ... but I don't SEE an EmployeeID!! Is it the ContactID?

I'm close to suggesting you hire assistance (or contact the original Developer). This may not be something which can be fixed easily. But I'm still willing to try a bit more.

LaRetta

[halepic]

Hi LaRetta, Thanks again for all of your help. I am unable to contact the original developer on this project, she was a student worker who has since moved on. I understand what you are saying about using meaningless IDs to create relationship. I am going to spend some time and go through each of the tables and fields to make sure that the fields are consistent. I'm sorry to have caused you frustration on this project. Let me know the next time you come to Eugene, I at least owe you a drink. Hal