Field relationship between databases?

[Booyah]

I hope I can ask this question in a manner that makes sense as I am brand new to FM. I have 2 existing databases. I am trying to modify them so they will communicate with each other. When I enter a number in a specific field, I need that same data to be entered into a coresponding field in a different database. Is this even possible? Thanks, in advance, for your effort.

[Matthew F]

Yes, it is pretty simple to create relationships between databases.

1. Define file reference (so one database will know where the other is located)

2. Define the relationship between the two tables of interest (Define database > Relationships)

3. Place the related field on the layout of the related table.

That's it!

[bruceR]

I hope I can ask this question in a manner that makes sense as I am brand new to FM. I have 2 existing databases. I am trying to modify them so they will communicate with each other. When I enter a number in a specific field, I need that same data to be entered into a coresponding field in a different database. Is this even possible? Thanks, in advance, for your effort.

We need to know more details; but no, it's not possible; and most likely it is not even a good idea.

In databases the general idea is - with certain exceptions - to have ONE location for a piece of information but be able to see it in other locations. Copying information from one location to another is a recipe for data disaster - the data gets out of sync.

Please provide more detail about what you are trying to do.

[Booyah]

Thanks for your responses. I must have asked my question badly because both of your responses seemed opposite of my needs and / or my solution. I needed data in field "A" of database "A" to match (in real time) the data in field "B" in database "B". I was able to sucessfully do this using a second empty table in database "A" as well as global fields in both databases. The totals in Database "A" (Inventory) are reflected and are not modifiable in database "B" (Inventory Totals). In this situation, it is a good idea as well as poses no risk to differing data because the data will always match in both databases. I realize that this could (should) be done as a single database using tables, but these are existing databases that I have been put in charge of. I will eventually combine this information and eliminate all the unnecessary extra databases. Thanks again for your efforts.

[Fenton]

Since you say that you are stuck with the "redundant" structure, you should consider what needs to be done to insure that the value in the field in database A is copied to database B, whenever A is modified. This requires that a script be run, because that is how you pass an actual value like this from one table to another. It is not going to happen otherwise, because data entry into a local field in one table does not trigger actions in another table.

This can be done either by carefully controlling the interface, so that the script runs when you leave the data entry layout, or by installing a plug-in which runs the script when you leave the field.

There are at least a couple of free ones which do this, such as "zippScript". However, it would then be your responsibility to insure that the plug-in was installed and working on every client machine (which could be done), and that it triggered properly in all circumstances. The script itself would be simple, so that's not the challenge.

Another possibility is that the value does not need to be passed. It is often fairly simple to use an Aggregate function, such as Sum(), across a relationship, to produce a dynamic total, from data in another table. The difference is that the result cannot be a stored, or indexed value. Which translates to substantially slower; but perhaps adequate for the use it's being put too.

[bruceR]

Thanks for your responses. I must have asked my question badly because both of your responses seemed opposite of my needs and / or my solution. I needed data in field "A" of database "A" to match (in real time) the data in field "B" in database "B".

This is not enough information. Please be much more specific. If you can't be more specific - don't expect much useful help.

The answers previously supplied were exactly appropriate to the question you asked. Your answer above indicates that you have not yet grasped some very basic fundamental principles of database operation.

[Emma in England]

Congratulations BruceR - you've probably frightened the poor guy off. Many of us come here knowing very little, and this can be frustrating for experts. Unfortunately most of us have to learn on the fly, coming to forums like this to help. We don't come to be told how stupid we are.

Please be a little kinder, life is grim enough...!

[bruceR]

Booyah needs to be courteous to his free helpers by providing more information, so that we don't have to play a prolonged bout of guessing games; and to question his assumptions about how to do things.

[Booyah]

Thank you...to those of you who made an honest and helpful attempt at assisting me with my obvious lack of useful skills with this amazing program.

EMMA...Thanks for sticking up for the "New guy"

Bruce, elitist and oversensitive is an interesting combination. I started my first post letting you know that I have no FM skills and that I hoped my question would make sense. I asked a very literal question in attempt to elicit a literal response. I asked "When I enter a number in a specific field, I need that same number to be entered into a coresponding field in a different database."

Your idea of being helpful was to immediately tell me that I had not provided enough information and that it was a bad idea. I responded again with an admission of ignorance and that my question must have been badly asked. Your response to this was, "This is not enough information. Please be much more specific. If you can't be more specific - don't expect much useful help. The answers previously supplied were exactly appropriate to the question you asked. Your answer above indicates that you have not yet grasped some very basic fundamental principles of database operation."

Well imagine that...A brand new FM user in the forum who has not yet grasped some very basic fundamental principles of database operation.

When another member called you on your treatment of a new member, your response was

Booyah needs to be courteous to his free helpers by providing more information, so that we don't have to play a prolonged bout of guessing games; and to question his assumptions about how to do things.

Amazing...I was courteous and appreciative in every post. You, on the other hand, seem to have a chip on your sholder.

Did you get all tingly after typing your responses? Do you get some kind of validation from being the smartest person in the room?

This new user with "no grasp", figured out my own solution which works correctly, securely and requires no extra scripting. This was done through trial and error and not through your rude and incorrect advice.

MY advice is that you ask yourself why you are spending your time here as a "Free Helper". Are you really here to help others or do you have some ego issues to deal with?

I don't know if I will find it necessary to ask for help in this forum again in the future BruceR, but if you do see my name in a future post, please feel free to ignore it.

Thanks.

[Booyah]

I have included a copy of the solution I came up with. This only worked after I created an empty table. Can someone give an idea why that is? Thanks again

dbtodbrel_booyah.zip

[bruceR]

Taking offense at straightforward advice is not helpful and all of the points I made still apply.

1. You have not communicated any meaningful detail whatsoever about your files.

2. By not communicating this information you make the job of people who are trying to help you more difficult and you make the likelihood that the answer will be relevant very small.

3. You need to take my comments less personally and more seriously. You need to apply yourself towards understanding basic relational concepts. It is good that you uploaded a file. But we see that it has no relations and has unnecessary tables.

4. The solution presented in your example is NOT likely to be a real world solution for your data problem. In particular, there are no relations established between File A and B.

5. The statements in File A and B are not accurate. The field in file B is the field from File A. It CAN be modified. It is the same field, merely viewed from another location.

Please answer the following questions:

1. What real life things (Sales? People? Your CD collection?) are represented by File A; and File B.

2. What is this system or solution about?

3. What is the real nature of the field(s) you are trying to replicate?

For instance, in invoice solutions, File A might be Contacts and File B might be Invoices and you might wish the address for Joe Smith in File A to appear in the Invoice to Joe Smith in File B.

There are a LOT of subtle considerations about viewing or replicating data and the answer to your particular problem requires that you describe your system in more detail.

See attached files. Note that the name fields in File B are related fields from File A. They are not "copying" anything. It is exactly the same field, merely displayed in File B.

datbaseAB.zip

Edited November 21, 2007 by Guest

[Booyah]

Taking offense at straightforward advice is not helpful and all of the points I made still apply.

Your advice was rude and elitist, as was the attitude with which it was given. Your points were unnecessary because you were overprocessing the question.

For all intents and purposes, I asked you what time it was and you began trying to teach me how to build a clock. I Just needed...the time.

I asked, "When I enter a number in a specific field, I need that same number to be entered into a coresponding field in a different database."

The solution presented in your example is NOT likely to be a real world solution for your data problem. In particular, there are no relations established between File A and B.

This IS the (temporary) real world solution for my data problem and it works WITHOUT relations between the files.

You need to take my comments less personally and more seriously. You need to apply yourself towards understanding basic relational concepts. It is good that you uploaded a file. But we see that it has no relations and has unnecessary tables.

I am slowly grasping the concepts as time allows. I do not, however make any portion of my living designing databases. People wouldn't take your comments personally if they were not presented in such a rude and condescending manner. As far as the relations, they are not necessary in this instance. The empty tables ARE necessary but I don't know why. (As referenced in my last post).

Please answer the following questions:

1. What real life things (Sales? People? Your CD collection?) are represented by File A; and File B.

2. What is this system or solution about?

3. What is the real nature of the field(s) you are trying to replicate?

For instance, in invoice solutions, File A might be Contacts and File B might be Invoices and you might wish the address for Joe Smith in File A to appear in the Invoice to Joe Smith in File B.

There are a LOT of subtle considerations about viewing or replicating data and the answer to your particular problem requires that you describe your system in more detail.

See attached files. Note that the name fields in File B are related fields from File A. They are not "copying" anything. It is exactly the same field, merely displayed in File B.

For God's sake. I didn't ask you to evaluate my system or make suggestions about better ways to transfer or store data. I just needed to know how to perform one...specific...operation.

The file I uploaded, does exactly what I asked...the way I asked it. You may not think it is useful but it fills exactly the need I have at the moment and that's why I posted here to begin with.

...but no, it's not possible

What I specifically asked for IS possible. That was all I needed. Now again, Please, just ignore any of my future posts.

Thanks

[Søren Dyhr]

I didn't ask you to evaluate my system or make suggestions about better ways to transfer or store data. I just needed to know how to perform one...specific...operation.

Hm... now is it you who deliberately ignore what Bruce started to say. Synchronization as such is avoided by all means in relational databases ... it doesn't help your case that you ignore his upload, before replying again (I know because I was the first to download his stuff)

However is there another angle to the question, suppose you far fetched have your argument in order to allow de-normalization of your data into a flat file structure. Should you use the separation model instead which means only one of the files are field source, where file B then features no fields of it's own, only the fields from file A - this means that Field B have absolutely no use, since it's all in there ... each file might have it own preventions instated to prevent update, so only one file has writing rights.

I've made a modification of you file, by removing all fields in File B. This is honestly the only way to ensure integrity to make the source of the data uniquely stored.

In the future when asking questions in this forum or whatever forum, attempt to limit the scope of the required guesswork by supplying us with a description of context and purpose, because advices often changes with just a tiny change of scale, methods are hardly ever universally applicable!

Developers here might instinctively normalize a situation, only few minutes later realize that a sligt denormalization serves the specific purpose better. All relational database tool are only asymptotically approaching relational theory ... some requires more shenanigans to behave expected in some task than others.

But by stumbling over the separation model as a newbe, have you thrown yourself into the deep end of the pool ... but which way you make ends meet is up to you, but to ensure integrity have each data only in one location, and use the referencing measures the tool have various provisions of.

--sd

modSD.zip

[LaRetta]

No personal attacks here please! Everyone has different posting styles and Bruce was not out of line with his response. Some of us are short and concise and brutally honest. Some of us are sweet and top it with sugar and smiles. ALL of our responses are valuable even if we disagree with each other.

Listen Booyah, Bruce responded to help you! Is that rude in your opinion? His style isn't Mr. Fluffy, but his words were true and I would have told you the same although I would have sweetened it a bit more as Emma would; particularly because you are new and because that is OUR style.

It is difficult when communicating only with words because the intent, the heart, and the emotional state of the other person can't be seen. And it is particularly difficult when you are new to a forum and don't know the people OR know the program! We ALL understand how frustrating it can be and we don't expect you to have all the answers or to even know the right questions. So when asked for more information, just try to rephrase or add some more detail and please don't take it personally. If Bruce didn't give a rip, he'd have walked away by now and he CERTAINLY wouldn't have provided a file to help you! I suggest you truly listen to those who have vastly more experience at FM than you do. Not surprisingly, they are 99.99% right. Hang in there, okay?

LaRetta

[bruceR]

Booyah: I went out of my way to help you and I provided a very useful example. What would be appropriate is a word of thanks and appreciation, not an attack, which is completely inappropriate.

No, the file you uploaded does not do exactly what you asked.

It does what **I** suggested which is to NOT copy data but instead view the original data in the other file.