concatenate a field based on two related ID fields

[NJHeart2Heart]

Not sure if that was a very clear title, but I wanted to be adequately descriptive.

 

I have a db with several tables, most of which are more reference tables to make data entry easier.

 

I have a table called shows, with each show a primary key.

I have a table called classes, each class with a primary key.

 

I have a Show|Class Join table where user chooses from drop down lists from each of the above fields as foreign keys, and also shows the "name" field vs. the number ID field.

 

The Show|Class table designates each show and class combination as it's own entry.  

 

I am trying to create a new field in this table which pulls the NAME data from each of my foreign key fields and concatenates it into "ShowName", "ClassName" with a space in between.  

 

If I just concatenate the fields as is, they show up as a combined text number, but I want the field to populate with the NAMES which are designated by those keys.

 

I couldn't upload the db file, but attached is my relationship report for my db.

 

Thanks in advance for your help.

 

Dawn in NJ

 

 

 

RelationshipsReport_2014-03-12.pdf

[comment]

I am trying to create a new field in this table which pulls the NAME data from each of my foreign key fields and concatenates it into "ShowName", "ClassName" with a space in between. 

 

If I understand this correctly, you want this field to be a calculation field (result type is Text) =

Shows::ShowName & " " & Classes::ClassName

You didn't say what you need this field for. If it's for display only, you could simply type:

<<Shows::ShowName>> <<Classes::ClassName>>

directly onto the layout, instead of adding a calculation field to your schema. See the help for "Placing merge fields on a layout".

[NJHeart2Heart]

Comment,

 

Yes, I intended a text calculation field.  

 

Ah! That's what I was missing.  I needed to designate it to pull from the table, than the "name" field of that table.  Thank you so much. I'll try that now.

I will look at the help regarding merge fields - I don't know what the difference is in mechanics/usability, but I'm anxious to learn.

 

Thanks,

Dawn

[NJHeart2Heart]

Hmmm...

 

I went to design view, then down to the field properties.  The calculated field fell under the "Expression" line item, but when I tried to enter the code manually, as written above, it said "could not find "field "ShowName".  I believe the tables are correctly related, as my two FK drop-down boxes fill in properly, and it shows the relationships on my relationship graph.

I then also tried to go through the "expression builder" (button on far right of the line item "Expression"), but that dialog box didn't seem to give me the option to pick from other tables, only the fields in the current table.

 

Regarding the question "calculated field" vs. merge field, I will be referring to that column in another Join table, called "ShowClassResults", which is meant to relate the newly created ShowClass IDS (referencing the named field "ShowClassName") with a particular item, which then results in a placing for that item.

 

Thoughts?

Dawn

[comment]

when I tried to enter the code manually, as written above, it said "could not find "field "ShowName".

 

I took the name from your PDF - which is not a screenshot, so your actual names could be different.  When you are in the 'Specify Calculation' window, you can select the related table (Shows) from the drop-down just above the field names (top left). Then double click the ShowName field (or whatever it's really called ) to insert it into the calculation formula.

 

 

That said, some of the terms you use are unfamiliar to me. Are you using the English version of the application?

 

 

I will be referring to that column in another Join table, called "ShowClassResults", which is meant to relate the newly created ShowClass IDS (referencing the named field "ShowClassName") with a particular item, which then results in a placing for that item.

 

I am afraid I couldn't decipher that. As a rule, you don't want to use names as matchfields for a relationship (because names can change). If you want to have a child table of ShowClass, use either the concatenation of the two IDs or an auto--entered serial number ID of the join table as the key.

[NJHeart2Heart]

Comment,

Thanks for sticking with me through this.  I am a beginner so my terminology is sketchy. I'm happy to clarify if you tell me what terms I'm using are confusing.  

 

I am using the english version of Filemaker Pro 13, for future reference.

 

You lost me at the "specify calculation" window.  I do not know how to get to that window.  Hopefully the screenshots attached will help you see where I am.  The only window I am able to bring up is called the "expressions builder"

Per my screenshots, the leftmost circle is where I tried to enter the calculation manually (no, the text in there is not what you gave me- it's from my first try).  when I typed the formula manually, I got the error message shown above that screen shot. 

 

When I went to the expressions builder window - via the button shown in the rightmost circle, I get the window shown below that screenshot, which doesn't appear to offer an option to select other tables.

 

 

access_screenshots.pdf

[comment]

I am using the english version of Filemaker Pro 13, for future reference.

 

But your screenshot shows you are using Microsoft Access. FileMaker Pro is a different application. Also, your profile shows Mac OS, but Access is a Windows-only application. You wanna tell us what's going on here? It's not April 1 yet, is it?

[NJHeart2Heart]

comment,

I thought I had written a response yesterday, but here it is again..

 

oh my.. I wonder if you've EVER come across this one...

 

It seems I've confused myself completely.  I have a mac laptop with FMPro13 installed at HOME, but at work, on my windows machine, I've been experimenting with the SAME database within MS Access! 

I apologize for wasting your time on this.  It was not my intention at all, and I certainly hope it hasn't ruined my ability to get help with FMPro on this board in the future, as I'm sure I'll come across roadblocks -  or maybe brainblocks is a better word for it!

 

Although.. it would have been a good joke IF it were April 1st!

Dawn

[comment]

So there's no hidden camera? I was sure this was either a prank or a test - and I am kinda proud I got on to you in my second reply ("Are you using the English version of the application?"). I would have got it in first, but I have never seen an Access RG before and I thought that was an ERD produced by some graphic application.

[LaRetta]

Hmmm...

 

The calculated field fell under the "Expression" line item ... I then also tried to go through the "expression builder" (button on far right of the line item "Expression"), but that dialog box didn't seem to give me the option to pick from other tables, only the fields in the current table...

 

This is where I became perplexed. 

 

We will all be happy to help with any questions in the future, Dawn.  Welcome to FMForums!!

[Mark Scott]

Dawn,

I'm certain that Comment and LaRetta will be happy to help you at any future time. You can see from their post counts that you've got two of the most knowledgable and helpful folks around these parts.

Here's my attempt to help: Ditch Access. Unlike Access, FileMaker is cross-platform, so that's one application to learn, instead of two, which you can then use on both/all of your computers (and iPhone, iPad, etc.). Also, unless you're already skilled in Visual Basic and comfortable with SQL, you'll have a much steeper learning curve ahead of you in Access—maybe not at first, but it'll get steeper once you move beyond the basics, with no more payoff than FileMaker at the end of the day.

Rest assured, however, that, either way, you'll always be welcome here with your questions, discoveries to share, help for others when you spot the need, etc.

Best,

Mark

[NJHeart2Heart]

Mark Scott and all,

Well, Here I am back again!!  I JUST read your kind post Mark, and thanks to you and all for you graciousness  I am back to Filemaker again and definitely have some questions which I'll post separately  Just wanted to acknowledge and appreciate you all for being so kind

Dawn