x Want to participate? Join the group to interact
  • State Verified Answer
  • Replies 4 replies
  • Subscribers 77 subscribers
  • Views 953 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
32
replies
General Discussion
6k
topics
18k
replies
Matthew Shaw

Javascript SQL Query

SOLVED
Posted By

Hi All

I'm trying to put a restriction on an Adv. Search Select filed, but I need to be able to do a sub query in the SQL look up and I don't know how to do this. On Cases I have created the Adv. Search Select field, but I want to lock it down to all records in a Custom Entity that match the company. But the Custom entity doesn't hold the Comp ID value, this is held in middle table (TableA) 

For example below are three tables and I need to get the records out of TableB going via TableA

Cases = case_primarycompanyid

TableA = TA_TableAID and  TA_companyid

TableB (the one I want to get to) = TB_TableBID and TA_TableAID

Parents
  • 0

    You can do a standard SQL join to join tables A and B by TA_TableAID.  But what is the advanced search select field bringing back?  Table B right?  Also, is this in a create script or in an asp page?

  • 0 in reply to GaryPerkins

    Hi Gary,

    Thanks for this. The idea is the company record holds multiple equipment records (e.g. TableB in this example). But this table itself doesn't hold the company id within it, only in some other 'middle man' table ( comp -> TableA -> TableB) 

    So on Cases I'm looking to create an Adv. Search Select field which looks up Equipment (TableB) only for the company to which the Case is associated with (case_primarycompanyid)

    It'll be in the CreateScript as the field is going on the standard Cases main screen

Reply
  • 0 in reply to GaryPerkins

    Hi Gary,

    Thanks for this. The idea is the company record holds multiple equipment records (e.g. TableB in this example). But this table itself doesn't hold the company id within it, only in some other 'middle man' table ( comp -> TableA -> TableB) 

    So on Cases I'm looking to create an Adv. Search Select field which looks up Equipment (TableB) only for the company to which the Case is associated with (case_primarycompanyid)

    It'll be in the CreateScript as the field is going on the standard Cases main screen

Children
No Data

*Community Hub is the new name for Sage City