Searching for records with related (portal) records

Classic Servoy
1

I would like to have a button on my main form that allows only the records that have related (portal) records to be displayed. I cannot figure out how to do this. Can someone point me in the right direction?

Also, is there a way to do a “find” (from the menu) on the information in a portal and return only the main records containing the desired portal information.

Thanks,
Jay

2

Just to be sure we’re thinking the same:
assuming you have a company list,
you’d like to have a button(search) that displays (for instance) only the companies that have contacts?

3

That is correct. We have a list of parts, and only some are on our published price list. I want to find only the parts that have price list entry information (contained in the portal).

Jay

4

//script in the main form (companies)
controller.find()
companies_to_contacts.contactsid = ‘>0’
controller.search()

assumption:
contactsid is a pk in the child table and
always has a value >0

5

Marten,

I must be doing something wrong. I tried what you suggested and did not see any results (method returned all parent records).

When I interactively do a “Find” I can only enter criteria in the parent record fields, not into the portal record fields, so I cannot simulate the search interactively. Is it correct that one cannot search portal records interactively?

I am stuck - seems like it should be easy to do. Any ideas?

6

are you really sure you did something like:
companies_to_contacts.contactsid = ‘>0’

and not
companies_to_contacts.contactsid >0

7

Maarten,

Yes - I entered it as shown below (= ‘>0’).

I thought perhaps that it should be the second way, then realized that we
were replicating what would be entered in the field while doing an interactive search.

Should I be able to search interactively for portal records?

Jay

8

Interactive search (I assume you mean going into find mode and entering’>0’ in the child record )should work as well.

This related search definitely should work.
Is your relation build up with integers?
Can you send me an example with some sample data?

9

Maarten,

When I do an interactive search on my form that contains the main item data plus the portal for the related child records I can only enter criteria in the parent record fields. There are no visible child record fields in which to enter a search criteria. Perhaps I have something set wrong?

I have two tables:

Items (parent on form)
PriceListData (child on form)

They are related through ItemID which is text field in the Items table (where it is the primary key) and a text field in the PriceListData table (where it IS NOT the primary key). This is a one (Item) to many (PriceListData) relationship.

I have tried searching on PriceListData.ItemID >0 and also PriceListData.PriceListDataID (the primary key) >0 with no success.

Is there an easy way to send you my solution file?

Jay

10

Hi Jay
If you export your solution with sample data, you can post it as a zipped attachment to this forum thread

HTH

11

Edward,
I can see how to export my data to a .xls file, however how do I export my solution?
Jay

12

Hi Jay,

Go to File → Repository and there you will find an option to export a selected solution.

Click on the solution & choose ‘export to file’ - remember that you must click on the version inside the solution !!

Name the exported file ‘filename.servoy’ and choose to export sample data from the dialogue.

Simple as that !

The exported file can then be sent to the forum as an attachment

Cheers
Harry

13

Hi Jay,

The fact that you are using textfields as keys in your relationship changes everything.
If you do a search on a textfield you can’t say “give me everything >0”.

This should work:
//script in the main form (companies)
controller.find()
companies_to_contacts.contactsid = ‘!^’
controller.search()

The search now looks for all related records where contactisid is not null.(in other words > DOES have a value)

(also press F1 (Help) and check the search data section)

14

Hi Maarten,

How can I do the opposite, let’s say I want to find parent records that have to child records.

Can it be done?

Thanks
Pavel

15

Assuming you want to go from contacts to companies,
create next method on the contact list form.

//create an array with all pk's of the parent records
var pkArray = new Array();
for(var i=1 ; i<=foundset.getSize() ; i++)
{
	var record = foundset.getRecord(i)
	pkArray[i] = record.company_id
}
// remove duplicate pk's
//this link explains next code [url]http://www.planetpdf.com/developer/article.asp?ContentID=6389[/url]
var unduped = new Object;
for (var i = 0; i < pkArray.length; i++) 
{   
	unduped[pkArray[i]] = pkArray[i];
}
var uniques = new Array;
for (var k in unduped) 
{
	var x = unduped[k];
	if(x)
	{
   		uniques.push(unduped[k]);
	}
}

//convert the uniquePkArray (uniques) into a dataset
var pkDataset = databaseManager.convertToDataSet(uniques)
//load the foundset in the parent form using this pkDataset
forms.companies.controller.loadRecords(pkDataset);
16

“No” records? Then add this to the end:

currentcontroller.invertRecords()

17

I’m so sorry Maarten, i meant no related records, thanks for the method i’m sure i’ll make use of it later.

Thanks for the tip david (why didn’t i think of it).

Thank you all.
Pavel