If a Market is for more than one county, does that require a separate record in MR for each county?
Is a county only ever a member of a single Market? or could a county be a member of multiple Markets in the MBM table? If you can answer these questions, I can describe a relationship that will work for what you need.
Then you need this relationship between MBM and MR:
MBM::__pkMBMID = MR::_fkMBMID
where __pkMBMID is a value that uniquely identifies each record in MBM. It is not a list of counties, but rather an auto-entered serial number or text generated by the Get ( UUID ) function.
Then a portal to MR on the MBM layout can list all MR records linked to that MBM. And Go To Related Records can be used to pull up a found set of all related MR records for a given MBM record.
Calculation fields could be defined in MBM that produce your multiple meeting fields in order to produce those columns of text in your exported csv file.
GetNthRecord ( MR::Meeting ; 2 )
For example would return the 2nd related record from MR for a given MBM record.
It will not surprise me to find out that you need to limit the listed MR data more than just by Market. You might, for example, need to limit this to meetings in a particular date range or all meetings that have not yet taken place. This can be handled with additional match fields used in your relationship.
Okay. I set __pkMBMID to be a serial number, _fkMBMID as text, and I've made the relationship. When I make a portal to MR on the MBM layout, nothing displays. I'm still confused how the relationship would pull the information I need. Here are my table occurrences:
If you are using records created prior to adding these fields, the __pk and _fk fields will be empty. You'll need to update them using Replace field contents before anything will show in that portal.
I thought that you said you were "starting from scratch"? If the only records in your table are those created during development, you can simply delete them all and start over. If you want to keep your existing MBM records, you can put __pkMBMID on your MBM layout, Show All Records and use Replace Field Contents to update your records with serial numbers.
_fkMBMID should be changed to be a number field.
If you have existing data in MR that you need to link to your MBM records, things get more complicated as you'll need to temporarily match records by county name. This will work well only if you do not have typos in your county name fields creating records with county names that don't match.
Here's how to do this:
Combo_County has a list of counties by name. We'll add a calculation fields that turns this into a list of county names separated by return characters so that we can use it as a matchfield to match to any MR record with a county name that matches any one of the counties listed in Combo_County.
Define cCountyList in MBM as:
Substitute ( Combo_County ; ";" ; ¶ )
Then, temporarily change your relationship to be:
MBM::cCountyList = MR::County
Then add _fkMBMID to your MR layout, Show All Records and use replace field contents with this calculation to copy the matching MBM record's serial number over into the _fkMBMID fields of the MR table:
Then, go back to your relationships graph and return your relationship to that shown in your screen shot.
That worked perfectly. Thanks so much for your help so far.
Next step: I need to create a layout that simply shows the State, County and Meeting Count. Every county within MBM either has 0, 1, or n meetings. If there is no corresponding county in MR, then that would mean zero meetings. One instance of the corresponding county would mean one meeting. Multiple instances of a corresponding county would mean n meetings. I cannot figure out how to count the meetings when there are more than one.
Sorting your MR records by MBM, then by County would group them by county. A summary field can Count a never empty field to show the number of meetings. If you replace the body of a layout based on MR with a sub summary part "when sorted by county", you'll get one row for every county that has had at least one meeting.
But that omits a county if it has add zero meetings,
To get one row for every county even if there were zero meetings would require adding a table of counties to link to MR by county. Your report could then be based on this new County table.