7 Replies Latest reply on Jan 30, 2014 11:08 AM by philmodjunk

    Linking two tables based on market location

    fmNOOB

      Title

      Linking two tables based on market location

      Post

           I apologize in advance if this is a stupid (and long) question. 

           My ultimate goal is to generate a properly formatted .csv which lists meeting information in chronological order for use in another process.

           I am starting from scratch. Right now I have two tables: MBM (which lists which Market [defined by state and county] will need meetings generated) and MR (which contains all the meeting data [dates, times, locations, including state and county]). The trick is that sometimes multiple counties are treated as a single Market in the MBM. There is a field (Combo_County) which designates whether two or more counties need to be combined as a single Market. If so, this field lists of all the relevant counties within the state separated by a semicolon. If not, this field is blank.

           So logically, it should work like this:

           1. Is there data in Combo_County in the MBM?

           If yes, pull all the MR records that match the (combo) Market in MBM into a third table (I think). If no, pull all the MR records that match the (single) Market in MBM into a third table (again, I think).

           2. In the third table(?), order each set of MR records that have been grouped by Market (single or combo) by Location (sometimes there are multiple locations within the same county), then by Date, then by Time. Then combine all of the ordered meetings for each Market into a single record (basically: Location; Meeting1; Meeting2; Meeting3...).

           Seems simple, but I'm still trying to figure out how to link the MBM and MR tables. Maybe we should take it a step at a time. How can I evaluate the MR records against the MBM Markets to group them in a third table? Any thoughts on effectively linking the two tables would be greatly appreciated.

           Thanks!

            

            

            

            

            

        • 1. Re: Linking two tables based on market location
          philmodjunk

               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.

                

          • 2. Re: Linking two tables based on market location
            fmNOOB

                 Thanks for your help.

                  

                 

                      If a Market is for more than one county, does that require a separate record in MR for each county?

                 A Market in MBM is either a single county within a state OR a combination of multiple counties within a state. For instance: Weber, Davis, Morgan are all single Markets. "Beaver; Box Elder; Cache" and "Carbon; Duschesne" are examples of different Markets made by combining counties. 

                 Each record in MR is a single meeting event—Date, Time, Location (including County and State). So, for instance: there are five different meetings in Weber—five different records that need to be sorted against each other (by Location, then Date, then Time); there are two in Beaver, four in Box Elder and four in Cache—ten different records that all need to be combined and sorted against each other (by Location, then Date, then Time).

                  

                 

                      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?

                 A county is never part of multiple Markets. It is either a single Market itself OR part of a combo; never both.

                  

            • 3. Re: Linking two tables based on market location
              philmodjunk

                   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.

              • 4. Re: Linking two tables based on market location
                fmNOOB

                     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:

                      

                • 5. Re: Linking two tables based on market location
                  philmodjunk

                       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:

                       MBM::__pkMBMID

                       Then, go back to your relationships graph and return your relationship to that shown in your screen shot.

                  • 6. Re: Linking two tables based on market location
                    fmNOOB

                         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.

                    • 7. Re: Linking two tables based on market location
                      philmodjunk

                           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.