2. PHPRunner General questions
  3. Draw currant field from Master Table into a Details Table
This topic is locked

Draw currant field from Master Table into a Details Table

 		3/21/2017 2:34:15 PM
PHPRunner General questions
H
 Hertz2P author

Need to set a WHERE clause in a Lookup Wizard to only show "Equipment_Names" from an "Equipment" Table that are currently at a certain "Current_Location" Field. I've linked to the "Equipment" Table, and the drop-down populates with all "Equipment_Names".
To mask all the selections that don't apply, I want a WHERE statement that only shows names from the "Location" of the current "Project".
My problem is that the current project isn't listed in the Active Table. The Active Table is a Details Table for a Master Table that has the needed "Location" data (providing the user has selected it from the drop-down prior to editing the Details Table.)
So I need to know the correct verbiage for the WHERE string.. Something like " Current_Location = ". $VALUE. [NameOfMasterTable].[NameOfProjectField]
I haven't been able find an example of what needs to go here, so I'm hoping someone can steer my in the right direction.
Thanks!
H
 		Hertz2P author 3/22/2017

I just want to clarify this because I'm pretty sure it is do-able, I just can't find an example in my searching..
My Master Table (Daily Report) has a field with drop-list to choose which "Project" the report is referencing. There is also a table that has all Projects in it. This is where the field lookup gets the data from.
I also have an "Equipment" table that lists all equipment, and one of the fields on it is "Current Location". This is a Lookup Wizard that also gets its values from that same "Projects" table.
There is a Details table nested in my Master (Daily Report) called "Onsite Equipment" it contains the fields "Type" and "Model". These are both Lookup Wizard Fields that get their data from the "Equipment" table. The "Model" field is contingent upon the value of the "Type" field.. Pretty straightforward stuff, and it all works fine now.
What I'm trying to accomplish is to restrict the choices for the "Type" and "Model" fields to only the Equipment that is currently at the Project. So, from the Details table "Onsite Equipment", I need to get the data from the Master Table (Daily Report) from the Lookup Wizard Field "Project" (provided that it has already been selected) and compare that to the "Current Location" field in the "Equipment" table.
I'm pretty sure that this can be done in a WHERE statement on the lookup wizard, but I don't know the syntax to do it. Does this sound familiar to anyone? Do you have some code that you can paste here that may be helpful? Can you think of another way to accomplish my goal?
Thanks!
M
 		Mark Kramer 3/22/2017

If I am reading this right, In your project "lookup wizard" for the table with equipment is a "Where" field. Here you can put something like this..... "Type like '%generator%' " or " Model like '%Case%' " It is a pretty basic "where" statement from sql.
I hope that helps.
H
 		Hertz2P author 3/22/2017



If I am reading this right, In your project "lookup wizard" for the table with equipment is a "Where" field. Here you can put something like this..... "Type like '%generator%' " or " Model like '%Case%' " It is a pretty basic "where" statement from sql.
I hope that helps.


Thanks for the reply Mark. I already have the lookup in 'Model' narrowed by the 'Type' field (Dozers, JD 450) or (Excavators,

JD 135) etc.. These work fine to narrow the possible results, but there's still way too many results to quickly file a report.
I'm actually looking to restrict these results to an exact match in a FIELD. This is actually something I do quite a bit. The difference here is that the linked field is not in the table I'm working in, but it is in its Master table. So I'm trying to pull that field from the Master table to put into the WHERE statement.
This way only equipment that has its "current location" set to the same Project can be selected. So when I foreman files a report, he only has to choose which equipment was used that was already on his job site, instead of muddling through every piece of equipment that the company owns..
I hope that's not too hard to follow, its harder to describe my goal in this than I had anticipated <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81746&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' />
Thanks again!
H
 		Hertz2P author 3/23/2017

Okay, here's a way to simplify the questions. I've got it working ugly now, by putting a 'second' project lookup wizard inside the Details table.. Everything works now as described above.
Does anyone know a way to copy the selected 'Project' from the Master table dropdown into the one on the Details table at the time it is selected? That way I could just hide the second one, and have the desired results.
Thanks!