Pulling column values from 1 table away

Classic Servoy
1

I need to make a comparison between a global value and field that related one table away.

I’ve seen the method posted by Maarten for pulling column values into an array; which is the following.

var maxReturedRows = 3;
var query = ‘select company_name from companies’;
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturedRows);
var array = dataset.getColumnAsArray(1);//put 1st column in array
// (or like this) var array = new Array(‘one’,‘two’,‘three’);
plugins.dialogs.showSelectDialog(‘Select’,‘please select a name’,array);

What I’m wondering is if there is a built in method for getting this information from the found set of a tab planel when the tab panel shows fields from a table related one away and a field one more away from that table - yeah, I know it’s confusing. For example this is what foundset returns when the button is clicked within the tab panel. But it doesn’t include the related fields for that form.

FoundSet[SIZE: 3,SELECTED RECORD: Record[COLUMS:pn_gid,pn_uid,pn_mid, DATA: Row[DATA:5,6,7, CALCULATIONS: {}]]]

I’m thinking something like this.

if (column.the_name.through_relationship.foundset contains global.checkvalue) {
show dialog “don’t need to add”;
return;
}

If this one is confusing then hopefully my picture will make sense.

2

Hi Matt,

I’ve attached a small solution that ,I think , simulates what you want.

Basically, it fills your valuelist (via a query) with all groups that are present in your groups tables (line 1) MINUS the groups that are already attached to the user. (line 3-7)

query:

  1. “select groupname, groupsid from groups where groupname not in”+
  2. " ("+
  3. " select g.groupname"+
  4. " from users u, memberships m, groups g"+
  5. " where u.usersid = m.usersid"+
  6. " and u.usersid = "+ usersid +
  7. " and m.groupsid = g.groupsid"+
    " )"

the method is triggered with:
onRecordSelection / onRecordSave.

note: create some records in groups and users before starting testing.

If you need more explanation please let me know.

3

maarten:
Hi Matt,

I’ve attached a small solution that ,I think , simulates what you want.

Thanks, I’ll take a look at it this week. I figured I could also use the copyAllRecords and used regex to spot for the id that corresponds to the name in the popup. Although I’m looking for as much learning as possible about methods of doing things.

Thanks!