AppGini Community Forums

I figured it out how to prepare a selection, but I do not know how to implement it in table tsoa lookup field definition in AG in order values will offer only desired requirements according category of preselected system. And the most user friendly solution should be if it can be reduced to the list only of those requirements which was not selected to the particular system yet, in other words only those requirements which combination of system + requirement is not yet in table tsoa.

Here is my selection example, tested on https://rextester.com/ simple copy & paste

Create table syst(
ID int IDENTITY primary key,
Sys varchar(20),
Catv varchar(4),
Cati tinyint
);

create table reqt(
ID int IDENTITY primary key,
Req varchar(20),
Cat tinyint
);

create table tsoa(
ID int IDENTITY primary key,
S int references syst(id),
R int references reqt(id)
);

insert into syst values ('A','II.',2), ('B','I.',1), ('C','I.',1), ('D','III.',3), ('E','II.',2)
insert into reqt values ('Firewall', 1), ('DLP', 3), ('BIA', 1), ('RA', 1), ('Backup', 1), ('Netflow', 3), ('Proxy', 2), ('DMZ', 2), ('NAC', 3), ('VPN', 3), ('OTP', 3), ('AD', 2), ('DHCP', 1), ('Syslog Server', 3)
insert into tsoa values (1,1), (1,2), (2,3), (2,5), (2,2), (2,4), (4,2), (4,4), (4,3), (4,1), (4,5), (5,7), (5,10), (5,1), (3,12), (3,11), (3,14), (3,13)

select syst.sys, syst.catv, reqt.req, reqt.cat from syst full outer join reqt on syst.cati>=reqt.cat order by syst.sys, reqt.cat

Hi,

not sure I get what you try to acheive. Which field is the lookup and to which source?

Finally I have found the solution, here is link to the great place for testers of coding in many programming languages:
https://rextester.com/discussion/ZOLIGE ... wo-tables-

Create table syst(
sid int IDENTITY primary key,
sys varchar(20),
catv varchar(4),
cati tinyint
);

create table reqt(
rid int IDENTITY primary key,
req varchar(20),
cat tinyint
);

create table tsoa(
id int IDENTITY primary key,
s int references syst(sid),
r int references reqt(rid)
);

insert into syst values ('Aaa','II.',2), ('Bbb','I.',1), ('Ccc','I.',1), ('Ddd','III.',3), ('Eee','II.',2);
insert into reqt values ('FW', 1), ('DLP', 3), ('BIA', 1), ('RA', 1), ('Backup', 1), ('Netflow', 3), ('Proxy', 2), ('DMZ', 2), ('NAC', 3), ('VPN', 3), ('OTP', 3), ('AD', 2), ('DHCP', 1), ('Syslog Server', 3);
insert into tsoa values (1,1), (1,2), (2,3), (2,5), (2,14), (2,4), (4,2), (4,4), (4,3), (4,1), (4,5), (5,7), (5,10), (5,1), (3,12), (3,11), (3,14), (3,13),(1,13),(1,14);

/* Register of all systems */
select sid,sys as 'System',catv as 'Category' from syst order by sys;

/* Register of requirements */
select rid,cat as 'Category',req 'Requirement' from reqt order by cat,req;

/* List of active requirements on system */
select syst.sys as 'System', syst.catv as 'Sys.Category', reqt.cat as 'Req.Category', reqt.req as 'Req.Active' from tsoa right join syst on tsoa.s = syst.sid left join reqt on tsoa.r = reqt.rid order by syst.sys,reqt.cat,reqt.req;

/* Register of all minimal needed requirements to system according system's category */
select syst.sid, syst.sys as 'System', syst.catv as 'Sys.Category', reqt.rid, reqt.cat as 'Req.Category', reqt.req as 'Req.Min.All' from syst full outer join reqt on syst.cati>=reqt.cat order by syst.sys, reqt.cat, reqt.req;

go
create view ReqMinAll as select syst.sid, syst.sys, syst.catv, reqt.rid, reqt.req, reqt.cat from syst full outer join reqt on syst.cati>=reqt.cat;
go

/* List of active requirements which are for higher category than system's categhory */
select syst.sys as 'System', syst.catv as 'Sys.Category', reqt.cat as 'Req.Category', reqt.req as 'Req.Active Over Sys.Category' from tsoa right join syst on tsoa.s = syst.sid left join reqt on tsoa.r = reqt.rid where reqt.cat > syst.cati order by syst.sys,reqt.cat, reqt.req;


go
create view ReqMinActive as select sys,catv,cat,req from ReqMinAll right join tsoa on tsoa.s=sid where tsoa.r=rid;
go


/*
List of all minimal missing requirements (or minimal requirements to add) a ccording system's category listed in view ReqMinAll,
as for example for System Aaa it should be only Backup, BIA, RA, AD, DMZ, Proxy in output, without any active requirements like DHCP and FW.

This is the solution

select sys,catv,cat,req as 'Req.Min.To.Add' from ReqMinAll A where not exists (select 1 from ReqMinActive B where a.sys = b.sys and a.req = b.req) order by sys,cat,req;


Compare Two Table using NOT EXISTS
The other faster method is to use the NOT EXISTS in WHERE clause of the query. This method is faster and performs well on the large volume of data.

Select id_pk, col1, col2,col,…
From table1 A
Where NOT EXISTS
( select 1 from table2 B
Where A.id_pk = B.id_pk
and A.col1 = B.col1
and A.col2 = B.col2
and…
);

*/

select sys,catv,cat,req as 'Req.Min.To.Add' from ReqMinAll A where not exists (select 1 from ReqMinActive B where a.sys = b.sys and a.req = b.req) order by sys,cat,req;

I created a view by the last select and I would like to add it as lookup advanced sql query or as a separate paralle window where user can select which row would like to add from parent table to newly inserted record in child table.

Hi,

and ... what is your question?
I have a similar setting (I think). I have a field ID_Location which takes the PK from the table ecomo_location - but - I am using a view as source. This works perfectly fine. (underlined blue: view name, underlined red name of the table (that actually exists in AG and database)):
Olaf