Relationships causing data entry not to work

[JenNicole]

Hello everyone! Once again, I have ran into a small problem that has now become a major hold up in the design of my database!

I have a table labeled [color:red]Source Codes, which identifies the payment entries origin. There is a [color:orange]source code(a mixed line of letters & numbers not exceeding 15 characters) and there is a [color:purple]source code description(a description of the code itself, the only thing all data entry users will see). There is another table labeled Payments, which is where the data entry user records members payments. In this table I have place a field for [color:purple]source code description. However, in doing so, the user was unable to enter anything into this field, let alone tab to it. The tab would automatically skip over it as if it did not exist! The only time entry was allowed at all was in an existing payment entry where you were able to edit it. Upon trying to solve this problem we discovered that each time a payment was put in, the [color:purple]source code description selected was added on to the bottom of the [color:red]source code table with a [color:orange]source code that did not match.

What is the deal here? How do I fix this? Any help will be much appreciated! Thanks in advance!

[bcooney]

It sounds like too much for guessing. Can you post a demo? Just clone the dbase, zip it and post using Manage Files in the Full Reply.

I understand that you wish to relate Source Codes to Payments, but it seems as if you should have a SourceCode foreign key in Payments, and an interface to populate it (popupmenu of value list that consists of Source Code & Source Code Description, showing only the second field).

Edited June 5, 2009 by Guest

[JenNicole]

I am hoping this works.....never tried to clone it to post on here. Let me know if you are not able to manipulate it. The scripts should be there as well. I cleared out all records except for 2 to test it.

Again....any help would be greatly appreciated!

JenNicole.zip

[bcooney]

I've finally had a chance to look this over. Oh, Jen, you need lots of help! Working in FM is deceptive. You look like you're making major progress, but you're building a house on a very flimsy foundation.

Why do I think this? You do not have a key field in every table. You have fields named RecNo, but they are not auto-enter serials. You have a table called DataEntry? Your relationship graph does not follow any best practice and is difficult to follow.

So, I would suggest posting what is called a ERD. It is a sketch, and you can use Layout Mode to draw one, of the tables that you need and how they relate.

Or, give us a paragraph, and we'll help you determine what tables you need and suggest names for them and how they should be related.

[JenNicole]

I would love to do this for you, however, here is my problem. I have to take an existing database built in SQL and C#, built by a retired engineer from Crane. The database he built for us is and has been a total nightmare! We originally worked with Filemaker, but he took our information from that and created this monstrosity that we are now dealing with on a daily basis. Now I am left to deal with his mess and try to create something close to what we had before when we first used Filemaker.

I have had trouble changing the way his tables relate when putting it into Filemaker due to the way he created his relationships. The way the relationships are in the tables I have placed into Filemaker is the exact same way he had his related in his database. When I try to change something about them, it changes everything and I will suddenly have missing information.

Therefore, I will gladly do what I can to make you better understand what I have here, but I am unsure of how to make the changes you are suggesting without having missing information in the end. I apologize for whatever headache this may cause but I am doing my best with what I have here. I will do my best to make some sort of a flowchart with these tables, but I am unsure of how successful I will be……..more to come. Wish me luck!

Until then, if there is some way to solve the source code issue, I will greatly appreciate any help you may have! Thanks again!