Expression box

[Nick Monsour]

Hi all,

I am trying to use an Expression Box to retrieve data from and SQL
database for reference by the user. I currently have the expression box
working however it only returns the first line from the database table
and I would like it to return all. My expression is:

LONG VERSION:

concat(xdXDocument:GetDOM("company_department_xref")/dfs:myFields/dfs:dataFields/d:company_department_xref/d:hr_Department/@department_name[../../@company_id
=
xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:hr_Department/d:company_department_xref/@company_id],
xdXDocument:GetDOM("company_department_xref")/dfs:myFields/dfs:dataFields/d:company_department_xref/d:hr_Department/@department_num[../../@company_id
=
xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:hr_Department/d:company_department_xref/@company_id])

SHORT VERSION:

concat(@department_name[@company_id = @company_id],
@department_num[@company_id = @company_id])

Can anyone please help i have been trying for days to either fix this
or do a work around and nothing is working well.

THank you,
Nick

 

[Andrew Watt [MVP - InfoPath]]

Hi all,

I am trying to use an Expression Box to retrieve data from and SQL
database for reference by the user. I currently have the expression box
working however it only returns the first line from the database table
and I would like it to return all. My expression is:

LONG VERSION:

concat(xdXDocument:GetDOM("company_department_xref")/dfs:myFields/dfs:dataFields/d:company_department_xref/d:hr_Department/@department_name[../../@company_id
=
xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:hr_Department/d:company_department_xref/@company_id],
xdXDocument:GetDOM("company_department_xref")/dfs:myFields/dfs:dataFields/d:company_department_xref/d:hr_Department/@department_num[../../@company_id
=
xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:hr_Department/d:company_department_xref/@company_id])

SHORT VERSION:

concat(@department_name[@company_id = @company_id],
@department_num[@company_id = @company_id])

Can anyone please help i have been trying for days to either fix this
or do a work around and nothing is working well.

THank you,
Nick

Nick,

What is it that you are trying to do? Please try to explain the
problem that you are trying to solve, rather than how you are
currently attempting to solve it.

Thanks

Andrew Watt
MVP - InfoPath

 

[Nick Monsour]

Hi Andrew (sorry it took so long I was sick)

I have tables in a SQL database Deptartment, Company, and Xref. All
companies have some departments but not all departments. So I want to
have a form that shows what departments currently exist for a selected
company populate an expression box the information I need to retrieve
is the department name and department number (this info is in the
Department table). Then in a section below I want to be able to have
the user choose an existing department and assign it to a company that
it doesn't already exist in and write this information to the Xref
table. I also want to include the functionality to be able to write in
a new department that doesn't currently exist with a new not previously
used department number. But I want to display what exists that way
there is less chance of duplicates.