2. ASPRunner.NET General questions
  3. [SOLVED]  Search Master and Details table at the Same Time
This topic is locked
[SOLVED]

 Search Master and Details table at the Same Time

 		2/13/2020 6:03:25 PM
ASPRunner.NET General questions
I
 i.NoLim author

For this project I'm currently using ASPRrunner.net 9.8. I would like to use the "main search field" to search the master and details tables together. For example, if I search for 'Jane' on the Family list page I should receive all the families who have an Individual with 'Jane' as her 'FirstName.'

  1. Master table - Family

    [list=2]
  2. FamilyID
  3. FamilyLastName
  4. Address



[]Details Table - Individual

[list=3]

[]IndividualID

[]FamilyID

[]FirstName

[]LastName

[]PhoneNo

[*]Email

[/list]

[/list]
They are linked using the FamilyID field.
I've looked at the manual and at this thread but I'm not getting any results when searching for an individual that I know exists. This is what I have so far after modifying Family(Master table) to add a dummy field named 'firstname.'



dynamic srchObj = SearchClause.getSearchObject("Family");
dynamic value = srchObj.getFieldValue("firstname");
if( value != null ) {

srchObj.setSearchSQL("firstname", MVCFunctions.Concat("FamilyID in (select FamilyID from Individual where FirstName like '%",value,"%')"));

}


Also, what exactly is happening in this part of the script? srchObj.setSearchSQL("firstname", MVCFunctions.Concat("FamilyID in (select FamilyID from Individual where FirstName like '%",value,"%')"));
I understand the Select statement; it's returning the FamilyID for all the entries in the "Individual" table where FirstName is the same as the term being searched for.
Thank you again.
T
 		Tim 2/18/2020

Hello,
I did get this working and it is very powerful. I use it all the time now. One thing to note is that you need to make sure to reference the master table correctly in the "dynamic srchObj = SearchClause.getSearchObject("Family"); " part. You might try "dbo.Family". Also, the dummy field is case sensitive, though it looks like you got that right.
The code says, if search field "firstname" is not null, then add (via concatenate) to the "where" statement in your original SQL. Here is an example of the resulting query:



SELECT Family.FamilyID

,Family.FamilyLastName

FROM Family

WHERE FamilyID in

(SELECT FamilyID

FROM Individual

WHERE FirstName like '%TextFromDummySearchField%')


Also, you may want to use "debug" to write the query to the page so you can see what's happening.
https://xlinesoft.com/asprunnernet/docs/after_application_initialized.htm
Good luck!

Tim
I
 		i.NoLim author 2/18/2020



Hello,
I did get this working and it is very powerful. I use it all the time now. One thing to note is that you need to make sure to reference the master table correctly in the "dynamic srchObj = SearchClause.getSearchObject("Family"); " part. You might try "dbo.Family". Also, the dummy field is case sensitive, though it looks like you got that right.
The code says, if search field "firstname" is not null, then add (via concatenate) to the "where" statement in your original SQL. Here is an example of the resulting query:



SELECT Family.FamilyID

,Family.FamilyLastName

FROM Family

WHERE FamilyID in

(SELECT FamilyID

FROM Individual

WHERE FirstName like '%TextFromDummySearchField%')


Also, you may want to use "debug" to write the query to the page so you can see what's happening.
https://xlinesoft.com/asprunnernet/docs/after_application_initialized.htm
Good luck!

Tim


Thank you so much for your response! Let me try this and I'll report back. Thank you again.
I
 		i.NoLim author 2/26/2020



One thing to note is that you need to make sure to reference the master table correctly in the "dynamic srchObj = SearchClause.getSearchObject("Family"); " part. You might try "dbo.Family". Also, the dummy field is case sensitive, though it looks like you got that right.



You were totally right! That's exactly what I was missing, "dbo.Family." Thank you again!