JOIN doesn't work with non-text fields

I’ve created two databases. (1) RMD dd test and (2) RMD client test.

Each has only ONE field: “RMD”
I’ve gone to: File > Database Options > Relations (tab) > and clicked matching fields “RMD” in both current database (RMD dd test) and source (RMD client test) and the “key” icon appears on both sides.
At the top of Relations menu it says “Join” “RMD dd test” with data from
“RMD client test”.

I want to replace Existing Data in current database. I’ve read through the instructions in “Specifying Data Transfer Connections” several times.

I’ve tried dragging the source field over to current.

When I double click Source to “Select formula for <> in database - > RMD test” the formula that pops up is simply “RMD”.

Should I edit this formula that pops up? If so, what do I edit it with? I should not use “yolk” as that appends rather than replaces and I wish to replace. Both field in current and source are “floating”.

The manual states: “For most relations you don’t need to set up data transfer for key fields.”

The following procedure in the current database does not populate the field “RMD” in the current database with the data in the source: join “RMD client test”

“Replace Existing Data” is checked in the Relations window.

All I want to do is populate all the data in the RMD field in the current database with all the data in the source.

Help anyone? Thanks

Relationship keys must be text. Floating point numbers won’t work. This hasn’t come up before, so I am now realizing that this restriction isn’t documented. Also, the dialog should not allow you to specify a numeric field.

In any case, it sounds like you don’t want to do a join anyway. You just want to import the second database into the first database, but using replace rather than append.

Thanks. I only want to replace (join/import) the ONE field “RMD”. I changed the field to text and still can’t get it to join. I even replaced numbers with names. The current database “RMD dd test” has only one field and it populated with the following 3 records:




The source database, “RMD client test” has the same ONE field “RMD” with 2 records”



The fields are linked in Relations with key icons on both current and source and “Replace Existing Data” is checked.

The procedure: join “RMD client test” with key icons checked in Relations does not do anything.

When I try: Import > Join Panorama Database with current database (choosing “Replace Existing Records” and “Match Fields by Name”)

and choose the source database then the field is populated correctly! Bingo! Except in my actual use, there will be several fields and I only want to populate that one field, replacing the data in that one field “RMD” in current from source.

Should the procedure specify the fields? As of now with my test there is only one field in each database but I can’t get it to join.

Jim has already made the important point that what you seem to want to do has nothing to do with relations or joining; you just want to import the two records from your source database to add to the three in your current database, to make five altogether. Provided the two databases have the same fields that is very straightforward. Joining is nothing to do with that.

A relation requires that the two linked databases share something in common. For instance, I have a recorded music catalogue in which people appear in various contexts: as composer, author, lyricist, arranger, orchestrator, etc., and/or as various kinds of performer, and/or as recording personnel (producer, sound engineer, etc.). Most people appear in more than record, all records feature more than one person in various roles, and one person might appear more than once in the same record with more than one role (e.g. as composer and performer). Thus I have a database of people, with one record per person (one field contains a unique key, and the others contain forename(s), surname, dates of birth and death, etc.). In the main database each field holding a person contains just his/her unique key, and his/her name and full details can be extracted in different ways from the database of people via the relation between one of the fields containing a person in the main database and the key field in the database of people.

Thanks but no. I don’t want to add at all. I want to replace amounts (numbers but it’s OK to use text). In the example, only Julie and Andrews would populate the field and Larry, Moe and Curly would be replaced. The fields in the two databases are the same (titled RMD). Thanks for your help.

Rereading your original post, I see you want to replace the three records in current with the two from source, so that both current and source contain the same two records — rather than concatenating current and source to give five records as I thought. That is still an import operation, the only difference is whether you append or replace. It’s still nothing to do with relations or joining.

Search help for ‘Import Database’ (to use the wizard) or the command ‘importdatabase’.

Please put the 2 files side by side and take a screenshot of them. (Probably Command-Shift-4 would work best for you) Then post the screenshot. I believe you are not using the word field correctly and that is causing confusion.

Field is the vertical “column” analogous to column in Excel or Numbers while record is the horizontal analogous to “row” in Excel or numbers: right? Fields have properties which you can customize. You can set up relationships/links on those fields from one database to another. I’ll take screen shots and post. What am I missing? Again, I’m trying to import/join the data from a field in one database to replace the same field in another. There will be other fields in the “current” database and I want those fields to stay as is. Thank you.

Thanks but as best I know, importdatabase will import the entire database while I only want to import/join only the date in one field from one database to the other. I think that’s why there is a “join” function. I’ll read what you suggested. Thanks

I mis-typed “date” it should have been “data”

Unfortunately, when I use import from Panorama database and choose the correct fields to import, it deletes all the data in other fields and those other field are essential. But thanks for your suggestions. Seems like the “join” feature is what’s needed which was Jim’s original suggestion. if I can figure out how to get it to work

Until you post what your databases look like, it will be difficult to assist. Do note that while it appears that the use of the word ‘field’ is correct, the post started out stating that there is only 1 field in each database. Apparently this is not true.

It’s true of the databases he used for testing. It’s not true of his actual databases. It’s not at all clear what relationship the actual databases have to one another. This appears to be related to another topic.

That is not possible. I don’t think any database program will do that, and I don’t understand why anyone would want to do that in a real application.

in my test to see if I could get the “join” feature to work there is only one field. I can’t get to work even if there’s only one field

The way a join works, it will be necessary to have more than 1 field. Can you post a screenshot of what you are trying to do?

If I had a database file (File1) with a single field, and wanted to replace the data in it with the data in another database (File2) with a single field. I would just delete File1 and rename File2 to File1. I doubt that is what you really want to do.

If you are trying to replace the data in one field of a multiple-field database with the data from another field of a multiple field database, you need to figure out which record of the first database corresponds to a particular record in the second database. The relation is that correspondence.

Depending on how you use these databases, there are different ways of doing this. If you are just doing this once, you could do a formulafill on the receiving field where the formula is a lookup. If things are going to be changing all the time, then maybe a relation is the way to go. But either way, you need to designate how the records are related.