11 Replies Latest reply on Jan 18, 2015 10:06 AM by dcbriscoe

    Make a calculation that looks at one field from a related table but displays two others

    dcbriscoe

      Title

      Make a calculation that looks at one field from a related table but displays two others

      Post

      I expect this is a very easy calculation, but I can't get it to work. I need to insert a citation (2 fields: Author & Year [of publication]) from my Bibliography Table into my main data table. The main data table already contains a link to the Title field of the Bibliography (via a drop-down), so it would seem fairly obvious to tell the Citation to check if the Title field of the main data table equalled the Title field of the Bibliography table and if it did to insert the Author & Year fields from the same table.

      I have tried it with If, Choose, Let and assorted internal permutations and nothing happens. The Citation field in the Bibliography table produces the correct result using: Authors & ", " & Year

      Please can someone give me the correct calculation as I have wasted days on this and the rest of my PhD is on hold until I can crack it. Many thanks!

       

        • 1. Re: Make a calculation that looks at one field from a related table but displays two others
          philmodjunk

          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.

          • 2. Re: Make a calculation that looks at one field from a related table but displays two others
            dcbriscoe

            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!

            • 3. Re: Make a calculation that looks at one field from a related table but displays two others
              philmodjunk

              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.

              • 4. Re: Make a calculation that looks at one field from a related table but displays two others
                dcbriscoe

                Dear Phil,

                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.

                • 5. Re: Make a calculation that looks at one field from a related table but displays two others
                  philmodjunk

                  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.

                  • 6. Re: Make a calculation that looks at one field from a related table but displays two others
                    dcbriscoe

                    Very sorry -- I did my best to explain. I will try to attach PNGs of the two tables and the Relationship schemata.

                    • 8. Re: Make a calculation that looks at one field from a related table but displays two others
                      dcbriscoe

                      And the third. (And the people who are lurking, please respect my copyrights).

                      • 9. Re: Make a calculation that looks at one field from a related table but displays two others
                        philmodjunk

                        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.

                         

                        • 10. Re: Make a calculation that looks at one field from a related table but displays two others
                          philmodjunk

                          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?

                          • 11. Re: Make a calculation that looks at one field from a related table but displays two others
                            dcbriscoe

                            Dear Phil,

                            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