If you have this relationship in Manage | Database | Fields:
MainTable::Title = Bibliography::Title
Then you can add any field from Bibliography directly to your MainTable layout and it will show data from the Bibliography record whose title matches the title selected in your drop down, provided that MainTable::Titlte is the field formatted with that drop down. No additional calculations--let alone If functions are needed. You can even combine fields into merge text if you need them to flow together on the layout.
If you do choose to combine the value of several fields from Bibliography in a calculation defined in the MainTable, you can use this notation: Bibliography::Authors & ", " & Bibliography::Year. This notation is created automatically when you select "Bibliography" from the drop down in Specify Calculation and then double click a listed field from that table in order to add it to your calculation.
Hi Phil, I'm afraid that has not worked. I had the link from Main Table to Bibliography as a lookup, but changed it (on a copy of the database!) to a calculation. I put in the notation you suggested and that is working fine and linking to the Bibliography table.
However, when I tried the second link for the Citation field with the notation you suggested, it does not pick up the data. I had it as a text field, but when I changed it to a calculation field, the program hung. I have also tried the Citation link as a direct connect to the Citation field in Bibliography, which shows the same data (no joy).
I also tried deleting the field completely and making a new one with the notation you suggested (again no joy). I have a value list for the Title link to the Bibliography Title field, and tried connecting that -- same result. I also wondered if I had connected the tables the wrong way around, so swapped them over, but again no joy. (The relationship was Bibliography > Main, but is now Main > Bibliography as it is for another set of relationships on a separate field). I have reconnected everything so that all the relationships work properly and the two tables are both showing the correct data. Have you any other ideas as to what I have done wrong please? Many thanks!
What is different between the first and second citation? Do they both refer to the same record in Bibliography or different books?
Are you trying to set up two citations for the same record?
How is your data structured here?
If a single record in your master table can match to more than one bibliography entry, you will need a different relationship. Either you have many bibliography records to one MainTable record or you have many Bibliography record to many MainTable records.
What does one record in your MainTable Represent?
If you have many bibliography records to link to many MainTable records, you'll need to set up a join table to manage the many to many relationship.
The citations in both tables are accessing the same data (that is Bibliography::Authors and Bibliography::Year) -- I don't really need the Citation field in Bibliography, but it's useful for cross-checking as all the other information relating to the Title is in that table.
Each entry in Master relates to one Title in Bibliography -- There can be up to 50 entries in Master that relate to one Title in Bibliography, but that is not a problem (as far as I am aware). Master::Title has a drop-down which connects as a calculation (see previous post) to Bibliography::Title.
Each entry in Master relates to a specific pot-stamp (decoration on a ceramic vessel). That entry connects to one site (where it was found, which is in another table) and one publication (Title). I can't connect an entry via the site because there may be up to 3 or 4 different publications relating to that one site, reflecting excavations over several/many years.
The entry in Master is connecting perfectly happily to the data in the Sites table. Hope that makes it clearer and many thanks again for your help.
It makes it confusing.
The calculation field I described would be defined in the Main Table, not Bibliography. You could define such a field in Bibliography, but my example was not written for that context.
Though I would use an ID field with an auto-entered serial number or Get ( UUID ) value instead of the bibliography title, linking master table to Bibliography by title will work. This is then a basic "many to one" relationship where many records in Main Table might link to the same record in bibliography, but any one record in Main Table only links to a single Bibliography record.
I do not see, from here, why this is not working for you. If you still cannot get this basic relationship to work, you'll need to describe what you have in Manage | Database and on your layout in much more details. A screen shot of Manage | Database | Relationships with commentary telling me what is what and a screen shot of where you have attempted to set this up on your layout--taken in layout mode, might clear up the confusion.
Very sorry -- I did my best to explain. I will try to attach PNGs of the two tables and the Relationship schemata.
Oh, it's only letting me do one at a time.
And the third. (And the people who are lurking, please respect my copyrights).
Sorry, I'm not a lawyer, but there's really nothing to copyright in what you have uploaded. There are many many database systems out there with much the same info as what you have uploaded.
First immediate fix: Your "citation" calculation should not need to refer to any other occurrence of citation. I would modify it to b:
Authors & ", " & Year
That reference to "citation 2" is likely why you had trouble with this calculation field evaluating.
I asked for "commentary telling me what is what with the occurrences of your relationship graph. Without that I must guess and might guess wrong.
It looks like this centers on the relationship between ARPS Pots and Bibliography 2:
ARPS Pots::Pubs2 = Bibliography 2::Title
As long as the value in Pubs2 exactly matches a Title in Bibliography, you can do the following:
Enter layout mode on a layout that specifies "ARPS Pots" in the "table:" indicator next to the layouts drop down in the status tool bar. Drag and drop from the field tool in status tool bar to add a new field to your layout and open the specify fields dialog. In the drop down in the top of this dialog, select Bibliography 2, to bring up a list of fields from that table occurrence. Then click to select the citation field. You can also use the text tool to put a text object on your layout and use Insert Merge Field from the insert menu to add this field as a merge field in much the same way.
PS, with these relationships, I'd also set a "unique values" validation option on Bibliography::Title as this field would otherwise be a source of trouble if you created two records in this table with the same exact Title.
For more on Table Occurrences, see: Tutorial: What are Table Occurrences?
YOU ARE WONDERFUL! I did all you suggested and now the citations are showing up -- I cannot say how grateful I am, because it's been a week since I got stalled on this, and it has just been SO frustrating.
On the copyright front -- yes, you are right. I think I actually meant privacy, but it was late and I was tired.
Many thanks again for all your help (and patience). Yours ever, Diana