Passing related values of a foundset as string

Classic Servoy
1

How to passed related values of a foundset into a column of the parent table as string?

I’ve been trying to find an answer in the forum, but I’ve been unsuccessful. I am sure the answer is there somewhere, but perhaps I am asking the wrong question to Google and in this forum’s search.

Anyway, I need to pass the related values from column-b1 and column-b2 of Table-B, into column-a3 of Table-A as a string (simple text).

Doing this is not complicated at all if Table-A and Table-B were related in an one-to-one relationship. The problem is that these tables are in an one-to-many relationship.

In other words, the records of Table-A has many related records in Table-B. I need to pass the values of the column-b1 and column-b2 (of each related record) into a the column-a3 (of Table-A).

IF the relation were an one-to-one relation, something like this would work (btw, all of the columns are of the same data type, text):

var var_rel = foundset.tableA_tableB_relation;
column-a3  = var_rel.column-b1 + " " + var_rel.column-b2;

The problem is that the relation is an one-to-many relation, and I need to pass all of the values of the columns b-1 and b-2 (of all the related records) of Table-B into the column-a3 of the Table-A (in a form of string, as simple text).

Any tip will be appreciated! Thank you.

Carlos

2

I think I found the way…

Using databaseManager, define a data set, then get from the data as text using:

.getAsText(column_separator,row_separator,value_delimiter,add_column_names);

Correct?

3

Gentlemen,

I figured this out. It was quiet simple. I’ll post what I learned later on - for benefit of future newbie’s

Have a great weekend!
Carlos

4

Something like this worked for me__*__:

function setOneToManyAsString(event){
	var vQuery = "SELECT Table-B.column-b1, Table-B.column-b1 FROM Table-B Table-B JOIN Table-A Table-A ON (Table-B.column-b_id = Table-B.column-b_id) WHERE Table-B.column-b_id = " + column-b_id;
	var vDataset = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), vQuery, null, 100);
	//controller.loadRecords(vDataset);
	foundset.loadRecords(foundset.tableA_tableB_relation);
	column-a3 = vDataset.getAsText(' ',' | ','',true);
}

* Assuming that the based form’s property dataSource is database.Table-A.

Carlos

5

Shorten it up by putting this in a column-a3 calculation:

var vDataset = databaseManager.convertToDataSet(foundset.crm_companies_to_addresses, ['line_1','line_2','city','state','zipcode']) 
return vDataset.getAsText(' ',' | ','',true);
6

Hi ,

We can do it with found set find and search also. Get the Table b found set and apply the conditions and then we will get all the column b1 and column b2 records and then we can convert foundset to dataset then use Dataset.getAsText(’ ‘,’ | ‘,’',true) and update to the column a3.

Use this if you are not willing to use calculation.

Thanks
Chaitanya s

7

Great tips guys. Thank you very much. I’ll put them in my notes.

However, I couldn’t use any of these two approaches for the issue I needed to resolve because the data were various levels deep.

The queries are pretty complex (no like the one in the example above) and I needed to do things like DISTINCT and ORDER BY, etc. I know Servoy offers way to do these things, but it was much more efficient and easier to do this by query -given the requirements (IMHO).

Again, thanks. Really good tips. Parsing data here and there is something new for me :-/

Best, Carlos