14 Replies Latest reply on Apr 11, 2014 3:34 PM by mjroberson

    Merge Field and Table Occurrence

    mjroberson

      Title

      Merge Field and Table Occurrence

      Post

           Hi,

           I am trying to get a merge field to work that is based on the use of a table occurrence.  I used the table occurrence to get two fields;  make and model of an irrigation controller.  When I'm collecting data I enter the make of the controller and the model field is then populated.  However, when I go to my report and insert the merge field with make and model it is blank.

           Any suggestions would be appreciated.

           thanks

            

        • 1. Re: Merge Field and Table Occurrence
          philmodjunk

               You'll need to describe the design of your layout and the table occurrence and relationships on which it is based. Otherwise we have no specific info to use in providing you with a suggested fix.

          • 2. Re: Merge Field and Table Occurrence
            mjroberson

                 For this piece of the db I have the following tables (fields);

                 Controller (controllerID, Make, Model)

                 ControllerMake (controllerID, Controller Name) where controller name is a list of 26 different types of controllers

                 ControllerModel (categoryID, parentID, ControllerModel)

                 TO makeControllerModel (same as above)

                 Table relationships are

                 ControllerMake(controllerID)=ControllerModel(parentID): One make can have several models

                 Controller(Make)=makeControllerModel(parentID)

                  

                 The look up portion works properly I just can't get the data to appear in a merge field.

                  

                 thanks

            • 3. Re: Merge Field and Table Occurrence
              philmodjunk

                   Yes, but your layout is based on one of these table occurrences. Which one? This is specified in Layout Setup | Show Records From.

              • 4. Re: Merge Field and Table Occurrence
                mjroberson

                     Under Layout Setup  Show Records From I tried all tables but the only one that gave a result was Controller and it only showed the had the ID number of the make and model, not the actual make and model.  The other tables showed nothing or stated unrelated table.

                • 5. Re: Merge Field and Table Occurrence
                  philmodjunk

                       Let's see if I can visually construct the tables/occurrences/relationships that you describe and use that as the context for my next questions:

                       I can only confirm:

                       Controller     ControllerMake-----<ControllerModel     TO makeControllerModel

                       Match Field.

                       ControllerMake::ControllerID = ControllerModel::ParentID

                       Your post were you list tables and relationships lists the relationship between controllerMake and ControllerModel twice and does not identify any other relationships. Nothing that you've posted identifies a relationship linking controller or TO MakeControllerModel to other table occurrences.

                       So there seems to be a lot of detail missing here. You might want to upload a screen shot of Manage | Database | Relationships.

                       Then, in your original post you stated:

                       

                            When I'm collecting data I enter the make of the controller and the model field is then populated.

                       In what table are you entering that data? Controller? ControllerMake?

                       How is that data "populated"? (there's more than one way)

                       and what is your report intended to show? (what's the over all picture here for that report?)

                  • 6. Re: Merge Field and Table Occurrence
                    mjroberson

                         Your configuration is correct for the relationship.

                         The data is populated in the Controller table via a value list.

                         The report is intended to show the name and make of the controller.

                          

                         thanks

                          

                          

                    • 7. Re: Merge Field and Table Occurrence
                      philmodjunk

                           But from what I have deduced at this point and posted in my previous response, Controller is not linked to any other tables in any relationship. That seems very unlikely to work for you.

                           

                                The report is intended to show the name and make of the controller.

                           Does that mean that your report shows data for a single controller?

                           That would seem possible with just a layout based on the controller table as it has fields for both make and model, but with no relationship linking it to the other tables, this seems unlikely to be all that you need for your report.

                      • 8. Re: Merge Field and Table Occurrence
                        mjroberson

                             The report is based on the controller table but it only shows the makeID # and model# (both of which were assigned when I imported the controller make and model into the table.  On the jpg it is shown on the 6th line as 17 (make) and 150 (model).  I tried the other tables associated with the make and model but no luck.

                             Seems like there needs to be a connection to get the name for make and model, not just the number but I am uncertain how to affect it.

                              

                              

                              

                        • 9. Re: Merge Field and Table Occurrence
                          philmodjunk
                               

                                     On the jpg it is shown on the 6th line as 17 (make) and 150 (model).

                               What jpg is that?

                               

                                    Seems like there needs to be a connection to get the name for make and model

                               which is precisely the point that I have been trying to make. I do not see ANY links from the other tables to the controller table in the information that you have posted thus far and thus a layout based on that table cannot link to data in any of the other tables.

                          • 10. Re: Merge Field and Table Occurrence
                            mjroberson

                                 Thanks, I've tried linking the tables but it has not worked.  Attached is the report.jpg

                                 Any advice on other ways to do what I'm doing?  What I want to do is look up a controller make that then gives me a list of available models.

                                  

                                  

                            • 11. Re: Merge Field and Table Occurrence
                              philmodjunk

                                   Sorry, but there is no report JPG attached to any of your posts.

                                   Let's try it this way: From your post you have these tables:

                                   Controller
                                   ControllerMake
                                   ControllerModel

                                   Please describe what one record in each of these tables represents.

                                   From the names, I would guess that in Controller you have one record for each Controller that you sell, buy, service or own with one record in ControllerMake for each different controller manufacturer and one record in ControllerModel for each model that you sell/buy/service/own.

                                   But in many(not all) possible ways that you might use such info, the data in ControllerModel and Controller might be identical. (It depends on whether one record in Controller represents one specific piece of equipment or all controllers of a specific make and model. In other words, if I have 5 controllers, make "acme", model "A" and 6 controllers, Make "pinnacle", Model "X", do I have 11 records in controllers or just 2?)

                                   So please confirm or correct my guesses as this will help me spell out the needed relationships for what you want to do here.

                                   Then, please outline what you want to see on your report. A list of controllers with data about the make and model included for each? Data for a single controller? or ???

                              • 12. Re: Merge Field and Table Occurrence
                                mjroberson

                                     In this application I go to a customers house and collect information on the make and model of the irrigation controller that they own - that can only own one controller.  I also collect information about the features of the controller that I use in making calculations.  I store all features in the Controller table and only use the make and model tables for lookup.  One Controller record will have only one make and model.

                                     Your "acme, model A" and "pinnacle, model X" would be 2 records.

                                     Controller - I collect information about the make & model (from the ControllerMake and ControllerModel tables) and other features that I use for making calculations and recommendations.  (number of programs, start times, management type, etc).

                                     ControllerMake - this is a list of controller makes that a customer might own.  I've got 26 makes. When manufactures make new makes and models I update their respective tables.

                                     ControllerModel - this is a list of the model that are associated with each make.  Collectively there are 276 models for the 26 makers. When manufactures make new makes and models I update their respective tables.

                                     The report will list the single make and model of the controller that the customer owns along with a phone number that they can call for help in programming the controller.  In addition, the report also has the results of calculations that were done in part from the controller's features.
                                      
                                     I've redone the report.jpg file, I hope it uploads this time.
                                      
                                • 13. Re: Merge Field and Table Occurrence
                                  philmodjunk
                                       

                                            Your "acme, model A" and "pinnacle, model X" would be 2 records.

                                       Actually, from the rest of the information and your sample report, it sounds like it would be 11 records as each record in controllers appears to be a specific, single device found at a specific customer's house. If you visit 11 customers, and they each have either the "acme, model A" or the "pinnacle, model X controllers, do you record that as only two records in the Controllers table? I don't think that would be the case here unless you have yet another table with a record for each customer that you link to records in the controllers table. If you really would use just two records in this example, I don't see any use for the ControllerModel table as all data in any one record would be duplicated by a single record in the Controller table.

                                       But in order for your report to show data from the ControllerMake and ControllerModel tables, you will need to use Manage | Database | Relationships to link them in relationships that match a given record in Controller to the correct record in ControllerMake and ControllerModel

                                       From the fields that you have listed, that would seem to require these pairs of match fields:

                                       Controller::Make = ControllerMake::Make (a given record in ControllerMake will link to all records in Controller with a matching value in the Make field.)

                                       Controller::Model = ControllerModel::ControllerModel (a given record in ControllerModel will link to all records in Controller with a value in Model that matches to its ControllerModel field.)


                                        

                                  • 14. Re: Merge Field and Table Occurrence
                                    mjroberson

                                         Hi, well after a week working on this I think I finally understand.  I got it to work and shows up in the report.

                                          

                                         Thanks for your guidance.

                                          

                                         Mark