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.
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
The look up portion works properly I just can't get the data to appear in a merge field.
Yes, but your layout is based on one of these table occurrences. Which one? This is specified in Layout Setup | Show Records From.
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.
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
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?)
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.
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.
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.
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.
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.
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:
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 ???
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.
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.)
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.