AnsweredAssumed Answered

Trying to create a difficult (for me) value list.

Question asked by SteveMartino on Oct 24, 2018
Latest reply on Oct 24, 2018 by SteveMartino

Hi Forum.  I'm trying to solve a problem creating a conditional value list, but apparently not the typical conditional value list.


The tables involved are JOBS which is a one-to-many to EQUIPMENT.




There are 2 types JOBS, "Heat" and "Commercial".

--"Heat" JOBS can have none, 1 or many pieces of EQUIPMENT.

--"Commercial" JOBS never have any equipment.


--The Interface--

There is no portal on JOBS to EQUIPMENT.   There's really no reason to look at EQUIPMENT from JOBS.  For the most part, they are entirely separate operations, except that "Heat" JOBS can have EQUIPMENT, and about a once a year report to set up maintenance.

Also when a new JOBS record is created, usually the EQUIPMENT record(s) are not created at the same time, but much later.


EQUIPMENT has it's own layout and multiple tables related to it.  On the EQUIPMENT layout I can easily add additional EQUIPMENT records (related to the same JOBS record).  I can also search via a global dropdown attached to a value list, for existing EQUIPMENT records.  All of that works fine.


--The Problem--

What I can't seem to do is to create a new related EQUIPMENT record, based on an existing JOBS record (that has not yet a related EQUIPMENT record), on the EQUIPMENT table. 

I'm trying to figure out a way to create a value list of:

JOBS records (name) that are of type "Heat", that are active (Inactive box not checked) and do not have a related EQUIPMENT record.  Basically need a list of those JOBS, so I can grab that primary key in a variable, create new EQUIPMENT record, set foreign key to variable, etc.


I thought I could create another set of TO's to do this, showing records where the primary key in JOBS doesn't match any of the foreign keys in EQUIPMENT, but that didnt' work.  I tried self join JOBS TO's, and filtering for all the above, but that also didn't work.  Maybe I'm just doing it wrong.  I wondered if I could do it with a SQL statement, but I woefully lack that skill.


The only thing that does work, and maybe the only way to do this, is for new record creation, pop open a card window, list view, based on all JOBS and have the script perform a find for:

"Heat", EQUIPMENT foreign key (omit), Inactive (omit).  Which does return the list I need.

From there I can just make each list view row a button that grabs the primary key from JOBS, closes window, and off I go.


Well if you got this far, thanks for hanging in.


Any thoughts or suggestions are always appreciated.