Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Want to Use a Calculated Field for a Self-Join Relationship


Recommended Posts

I have a database (FM Pro 8.0) where I want to use a calcuation to create a self joint relationship. The calculation itself takes a ID field from the table that the calcuation field will reside in and appends it to a related field in another table.


The calculation itself works fine. But when I try to do the relationship - the relationship does not work. I think it has something to do with the fact at FM Pro will not let me index the calculation (an error box pops up when I try to uncheck the "Do not store results" check box.


I would appreciate any guidance to solve this problem.

Link to comment
Share on other sites

The calculation itself takes a ID field from the table that the calcuation field will reside in and appends it to a related field in another table.


That constitutes one side of the relationship. Are you using the same field for the other side of the relationship as well? (You're right, calc fields referencing values from other tables cannot be indexed).


Can you explain a bit what you're trying to accomplish under which circumstances? You're trying to join Record x in Table A to itself, either all of the time or only under certain specific circumstances (i.e., the rest of the time you want the relationship to fail). If you want it to work all of the time, there's no reason to reference any outside field. If you want Record x to make a valid selfjoin only when the value of Field 1 in Table B contains "foo", try this:


Case(Table B::Field 1="foo", Get(RecordID))


call that field "RecID_if_Foo".


Now make a second calc field, which is simply Get(RecordID), call it "RecID".


Make your selfjoin from RecID_if_Foo to RecID. This relationship will function because RecID is indexable, and it will cease to be valid for the record in question if Field 1 over in Table B ceases to contain "foo".


(Note that if the active window is turned to a Table A layout with a portal to the same record via the selfjoin relationship, and you change "foo" in the background, you will probably need to force a screen refresh to make the portal catch up to the change visually)

Link to comment
Share on other sites

Thank you for trying to help me. I should have put more detail in my first post.


There are a number of relevant tables


Table 0 - contains 2 fields: Company Name and Company ID

Table 1 - contains records of activity;

Table 0 and Table 1 are related - each activity has one company associated with it.


Table 2 - each record describes the group that conducted the activity (there can be more than one group that conducts a particular activity)

Table 1 and Tabe 2 are related


My goals is to generate a report that summarize by Company the number of activities associated with the Company (for each Group). For Example:


ABC Company Inc. - Group A: 12 / Group B:0 / Group B: 2

BSA Company Inc. - Group A: 1 / Group B:23 / Group B: 5


I have created a calculation on Table 2 that combines the practice group ID (found on Table 2) and the relevant Company ID (found on Table 1). I wanted to create a self join of that field - and then use the Count function to count the number of instances that a give Group has devoted to a certain Company.


Any thoughts?

Link to comment
Share on other sites

Your goals indicate a lack of any need (or use) for a selfjoin. (Well, I won't rule out the possibility that there exists a way to do it that utilizes a selfjoin, there are a million ways to do almost anything in FileMaker. But it sure would be counterintuitive to me).


I assume Activities are related to Companies via Company ID


Company ID in Companies::Company ID in Activities


I assume also, since each Activity can be "of" (or associated with) multiple groups, that your relationship between Activities and Groups relies on a multi-key? A Text field in the Activities table that is related to whatever your unique identifier is in the Groups table (Group ID or Group Name or whatever)?


Groups Field in Activities::Group ID in Groups


So for some hypothetical Activities record you have values like this:


Company ID: 39105


Activity Name: Doing Cartwheels


Location of Activity: The Front Lawn


Date of Activity: 7/3/2008


Then in the Groups field values would be separated by a hard return like so:







So this one activity is linked to the group that has Group ID 99, and the group that has Group ID 207, and 41403, and 2265.


My goals is to generate a report that summarize by Company the number of activities associated with the Company (for each Group)


You want a report on all Companies (or some subset that you did a Find for) and for each Company you want your report to show the quantity of Activities pertaining to each possible Group?


I see from your brief sample data that you expect the report to generate a 0 in Group categories for which there is no data pertaining to that group. So either you're working with a small, seldom-changing, very finite list of groups, where it makes sense to hardwire your script and your report layout to a specific set of groups, or else you're not. If you're not, you want to peel through the list of all the Groups in the Groups table, which means that your report will have to be "assembled" as a verb not a noun.



Let's tackle the first possibility first.


Assuming that your Group list is short and finite (Group A, Group B, Group C):



Make three calc fields, of type "Number", in the Activities table, like so:


GroupA_Co = Case(Group ID = 123, Company ID)

GroupB_Co = Case(Group ID = 456, Company ID)

GroupC_Co = Case(Group ID = 789, Company ID)


While you're in there, make a field called "One", calc, of type "Number", the formula of which is simply =1



Now you make three relationships, one from Company ID in Companies to each of the three new calc fields, resulting in three new table occurrences for Activities that are related to Companies as follows:


GroupA_Actv CompanyID::GroupA_Co

GroupB_Actv CompanyID::GroupB_Co

GroupC_Actv CompanyID::GroupC_Co


Having done so, create three calc fields in Companies:


GroupA_ActvSum = Sum(GroupA_Actv::One)

GroupB_ActvSum = Sum(GroupB_Actv::One)

GroupC_ActvSum = Sum(GroupC_Actv::One)


Now for any given company, you've got instant headcounts of activities in each group. This "report" exists as a "noun", it's just "there" automatically and immediately for any company you pull up.




In my next post I will address the messier case where you're adding and deleting groups to your group-list willy-nilly and can't use a hardwired approach like that.

Link to comment
Share on other sites

If you've got a long long list of Groups and/or the Groups that exist are in relatively constant flux, you need to assemble a report using a script.



Now, that's assuming you do indeed want Activity X to appear once under SomeCompany below the Group A header because it is a Group A activity, and once again under the Group W header (lots of groups, remember?) because Activity X is also a Group W activity. And/or it is assuming that you do indeed want to see Group B activities for SomeCompany reported as 0 because there is a Group B amongst your existing groups even though there are no Group B activities for SomeCompany. Are you with me?


If you can forego both of those requirements, you can use a straightforward subsummary report within the Activities table. But a subsummary report isn't easily disposed to display the same local datapoint multiple times, nor is it useful when you want to display categories that ain't got no data in 'em.



So, proceeding with our assumptions: we need a matchfield in Activities that matches the combo of Group ID and Company ID, a calc field of type "Text", like so:


CompanyID & "|" & Group ID


Call that field "Co|Group ID".


While we're in there, a calc field, of type "Number", =1, named "One".


We also need a global text field in the Companies table Call it "g.SelectedGroupID". And a calc field that concatenates that global value with the company ID, like so:


Company ID & "|" &g.SelectedGroupID


call that calc field "Selected Co|Group".




Now off to the Relationships tab.



Make a relationship between Selected Co|Group and Co|Group ID, which will produce a new Table Occurrence for Activities. Let's call it "CurrentCoGroup".


Make a relationship between g.SelectedGroupID and GroupID in Groups. This produces a new table occurrence for Groups, call this one "SelectedGroup".





OK, off to the ScriptMakerâ„¢.


Firstly, our script needs to snag the values of all Group IDs. (Or Group Names, but I'm going to use Group IDs in my example). You could loop through the Groups file, but it's easier to do it by setting up a Value List, "GroupIDs", by field, Group ID in Groups, all values; then you can do this:


Set Variable [$GroupIDList, ValueListItems(Get(FileName), "GroupIDs")]



Actually, you DON'T do that, not exactly. You do this instead, for reasons that will become apparent momentarily:


Set Variable [$GroupIDList, "¶"&ValueListItems(Get(FileName), "GroupIDs")&"¶"]





Now where are we, what table/layout are we on? Assume that the script is executed from a layout belonging to the Companies database, or else go to such a layout now. Assume also that we already have a found set consisting of the companies we want to report on, or else put in a Find routine so the user can bring up the right companies. Assume that all that is done.



Now it's time to go Looping and text-Positioning and Middling!



Set Variable [$GroupReport, $GroupReport&Left("¶", Length($GroupReport))& "COMPANY: "&Companies::Company Name]
Set Variable [$Pos, 1]
  Exit Loop If [GetAsNumber($Pos) ? PatternCount($GroupIDList, "¶")]
  Set Variable [$CurrentGroup, Middle($GroupIDList, Position($GroupIDList, "¶", 1, $Pos)+1, Position($GroupIDList, "¶", 1, $Pos+1)-1)
  Set Field [g.Selected Co|Group, $CurrentCompany & "|" & $CurrentGroup]
  Set Variable [$GroupReport, $GroupReport&"¶"&
      "GROUP: "&SelectedGroup::GroupName&" -- "&Sum(CurrentCoGroup::One)
  Set Variable [$Pos, GetAsNumber($Pos) + 1]
End Loop
Go to Record [Next, Exit after Last]
End Loop



OK, now assuming I didn't make a typo or have a brainfart in there somewhere, that goes to each company, writes the company name to the report, and sits there while it loops through every possible group, writes the group name to the report and how many activities exist for that group x company combo. Then it goes to the next company.


Now all your data is belong to ...


sorry ;)


All your data is now in a variable, $CurrentGroup.



You have choices: you can



a) Set some global text field to $CurrentGroup and print a page with that global field positioned as a merge code, just typing the field name between double brackets right on the layout like so, in Layout Mode:





There will be "resize handles" surrounding ">" on your layout if you single-click on it. Do so and grab one of the bottom resize handles and yank down to the bottom of the screen. In fact, make the layout very tall, taller than normal page size, and drag the handles to the bottom. Then single-click that sucker again, go to the "Format --> Set Sliding/Printing menu command, and check the checkboxes and radio buttons for "Sliding up based on" and "Also reduce the size of the enclosing part".


That's the page you would print.



OR, instead, you can


b) Again set some global text field to $CurrentGroup but this time, after doing so you isolate the current record (Show All Records, Omit Record, Show Omitted Only) and then export records, using just that one global field in your export order. Then you can format it.



OR, if you really want to go to town, you can even


c) Take the results of a single run of the report, format them just as you like in a word processor that can save in ".rtf" format, save it as such, open the result in a raw text editor such as NotePad or BBEdit or TextWrangler, examine the godawful mess of RTF code and then modify your looping script to assemble formatting codes in the global field as you assemble it, starting off with the boilerplate font stuff and then interspersing the line cut codes and formatting codes with the FileMaker data. Then you set your script up to export that field contents just as you did in b) above, but you give the exported text field a name ending in ".rtf" and when you double-click it on your Desktop it opens in Microsoft Word (or Apple TextEdit or whatever) fully formatted as you set it up to be. Time-consuming to set up but you only have to set it up once.

Link to comment
Share on other sites

Danged vBulletin!


In the Looping script where you see:


Exit Loop If [GetAsNumber($Pos) ? PatternCount($GroupIDList, "¶")]



That question mark was supposed to be a greater-than-or-equals sign. Sorry 'bout that, vBulletin didn't like it.

Link to comment
Share on other sites

Yeah, I should know that by now (I've been on vBulletin-powered boards before. In all honesty, it's great board sw, very robust. Like Joe Walsh though, I can't complain but sometimes I still do...)


Heh heh... I know how to do ≠ though!

Link to comment
Share on other sites

hey can you share that? btw, the smilies are a little awkward here. Yeah is a banana. Perhaps it's meant as an incentive to use the king's english (whoever he is).

Link to comment
Share on other sites

I was wondering about that banana.


≠ is


& # 8 8 0 0 ;


without the spaces in between. Don't preview it before posting or it converts it once for preview and then ruins it when you post it.


I have a QuicKeys macro set to option-equals that tosses that in there. Too many vBulletin board situations where I really wanted to say something (philosophical, political, otherwise opinionated) such as:


"Diagnosed with a label ≠ understood and explained"




"Microsoft does that" ≠ "Standard"

Link to comment
Share on other sites



Wow! Thank you so much for a thorough response to my question. After reviewing the options, I decided to go for the first "hard wired" approach (at least in the short term).


Unfortunately, I seem to have made a misstep somewhere.


(1) I created the two new calcuation fields on the Activitities Table (GroupA_Co and One). [My Group ID is actually from a related file] I created a new layout to test it and everything looked good.


(2) I created a new relationship from Company Table (field: ID) to Activities Table (field: GroupA_Co), as well as creating a new field in the Company Table (Sum).


When I am in a layout based on the Company Table, whenever I try to access a field from the Activities Table, FM responds with a "" message. (At the same time, when I am in a layout based on the Activities Table, I am able to access fields in the Company Table)


What should I do?

Link to comment
Share on other sites

What do you mean when you say your GroupID "is actually from a related file"?


Each record in Activities is related to various Groups records, yes? So you've got some kind of field in Activities that's really in activities that hooks that record to the various Groups records, yes? It is that field, the (presumably indexable) field in Activities that relates Activities to Groups that I refer to as Group ID.


You can't use an undexable field for the process I'm describing here because it will be on the right side of the relationship from Company.

Link to comment
Share on other sites

Thank you for your patience in helping me.


Let me redescribe my tables and see if that helps.


Company Table

* Company ID

* Company Name


Activity Table

* Activity ID

* Reference Field to Company ID


Group Instance Table

* Instance ID

* Reference Field to Activity ID

* Reference Field to Group Name


Group Name Table

* Group Name ID

* Group Name


Each Activity record is associated with only one Company record. Each Activity record can have one or more groups involved in the activity; so there can be one or more records of the Group Instance Table associated with an Activity Record. The Group Name Table merely provides the name of the Group associated with a given Group Name ID

Link to comment
Share on other sites


  • Create New...

Important Information

Terms of Use