Hi all

I am extremly new in Baan 4 Programming:(, can someone show me how to populate a field with select query for example:

select tiitm001.pris

from item

Is that possible to make a field run this query to get a list of item price

Thanks

Hi all

I am extremly new in Baan 4 Programming:(, can someone show me how to populate a field with select query for example:

select tiitm001.pris

from item

Is that possible to make a field run this query to get a list of item price

Thanks

Hello,
you have to select all fields you need.
eg:

select tiitm001.item, tiitm001.dsca, tiitm001.pris
from tiitm001
where <your condition>

Regards

Hi Bernd
So that means in Baan, whatever which field(in baan database) I want to populat to the field (in the form), I have to select all the fields first and then choose which field I want to use under the where clause

regards Billy

No, You can select the wanted field only:

select tiitm001.pris
from tiitm001
where <your condition>

Here is a link for the baan sql documentation (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_baan_sql).

I hope it helps,

- George

Thanks guys, that is far too helpful

Hi, I have a similar request and am also pretty dense when it comes to development side of BaaN IVc.

I need to display an Item Description, Customer Code and Customer Description on a form.

The current script is as follows:

|* ticsto500 0 VRC B40C c test
|* Display Material Quantities Issued Per P/O No
|* Stuart Orr
|* 1998-06-24
|* Main table ticst001 Estimated and Actual Material Costs, Form Type 3

declaration:

table tticst001 | Estimated and Actual Material Costs
table ttisfc001 | Production Orders
table ttcmcs003 | Warehouses
table ttiitm001 | Item Master
table ttcom010 | Customer Master

I have added the form fields required, formFields.jpg.

If very sketchy SQL skills are to be trusted the statement would look like this.

SELECT tiitm001.dsca, tititm001.cuno
FROM tiitm001
WHERE tiitm001.item = tisfc001.mitm
SELECTDO
SELECT tccom010.nama
WHERE :tiitm001.cuno REFERS TO tccom010
ENDSELECT
ENDSELECT

Which should return the 3 fields i am interested in. The question is how do I get these fields back to the form. does the select have to be included in the script in a function. This mod would make a big diff to our production guys so any help would be appreciated.

Thanks,

Mike

I would do this:


SELECT tiitm001.dsca, tiitm001.cuno, tccom010.nama
FROM tiitm001
WHERE tiitm001._index1 = {:tisfc001.mitm}
and tccom010.cuno = tiitm001.cuno
as set with 1 rows
SELECTDO
ENDSELECT
display.all() | Display fields on form.


Recommend always using an index if possible.

Hi, I have a similar request and am also pretty dense when it comes to development side of BaaN IVc.

I need to display an Item Description, Customer Code and Customer Description on a form.

The current script is as follows:

|* ticsto500 0 VRC B40C c test
|* Display Material Quantities Issued Per P/O No
|* Stuart Orr
|* 1998-06-24
|* Main table ticst001 Estimated and Actual Material Costs, Form Type 3

declaration:

table tticst001 | Estimated and Actual Material Costs
table ttisfc001 | Production Orders
table ttcmcs003 | Warehouses
table ttiitm001 | Item Master
table ttcom010 | Customer Master

I have added the form fields required, formFields.jpg.

If very sketchy SQL skills are to be trusted the statement would look like this.

SELECT tiitm001.dsca, tititm001.cuno
FROM tiitm001
WHERE tiitm001.item = tisfc001.mitm
SELECTDO
SELECT tccom010.nama
WHERE :tiitm001.cuno REFERS TO tccom010
ENDSELECT
ENDSELECT

Which should return the 3 fields i am interested in. The question is how do I get these fields back to the form. does the select have to be included in the script in a function. This mod would make a big diff to our production guys so any help would be appreciated.

Thanks,

Mike

----------------------------------------------------------------------------------------------
Hello,
let me try to help you:

What's the main table, you use ?
If tisfc001 (production orders), you only can get customers if there's a project. Otherwise the production is anonym.

based on the form field tisfc001.mitm , tisfc001.cprj you can try like the following:


table ttipcs020
table ttipcs021
extern domain tcnama customers.name

field.tisfc001.mitm:
before.display:

select tiitm001.dsca
from tiitm001
where tiitm001._index1 = {:tisfc001.mitm}
selectdo
endselect

field.tisfc001.cprj:
before.display:

if not (isspace(tisfc001.cpr)) then
select tipcs020.cuno
from tipcs020
where tipcs020._index1 = {:tisfc001.cprj}
selectdo
select tccom010
from tccom010
where tccom010._index1 = {:tipcs020.cuno}
selectdo
customers.name = tccom020.nama
endselect
endselect
else
customers.name = ""
endif

!! it's written from my mind, not tested !!

Regards

Hi Bernd, thanks for the reply... we don't use projects, as we are an engineer to order company each item code is specifically linked to the customer on the item master. Do i need a display some where in the code?

thanks,

Mike

Hi Mark, thanks for the reply, which event would i use in the Form section, i.e. init form, before display etc...

The form allows the user to search for additional production or have attached a copy of the form to give an idea of what front end i am working with.

Thank,

Mike

Hi Bernd, thanks for the reply... we don't use projects, as we are an engineer to order company each item code is specifically linked to the customer on the item master. Do i need a display some where in the code?

thanks,

Mike

Hello Mike,

how do you link the customer to the item master ?

Regards

We have a customised field tiitm001.cuno.

Mike

I typically chose the before.display on the main field - in this case tisfc001.mitm.

Hi Mark, have included the code as below, the following

|display.all() | Display fields on form.

If not commented out results in a recursion error, please see the attached.

Thanks,

Mike


|******************************************************************************
|* ticsto500 0 VRC B40C c test
|* Display Material Quantities Issued Per P/O No
|* Stuart Orr
|* 1998-06-24
|******************************************************************************
|* Main table ticst001 Estimated and Actual Material Costs, Form Type 3
|******************************************************************************

|****************************** declaration section ***************************
declaration:

table tticst001 | Estimated and Actual Material Costs
table ttisfc001 | Production Orders
table ttcmcs003 | Warehouses
table ttiitm001 | Item Master
table ttccom010 | Customer Master

field.tisfc001.mitm:
before.display:

SELECT tiitm001.dsca, tiitm001.cuno, tccom010.nama
FROM tiitm001
WHERE tiitm001._index1 = {:tisfc001.mitm}
and tccom010.cuno = tiitm001.cuno
as set with 1 rows
SELECTDO
ENDSELECT
|display.all() | Display fields on form.

|****************************** form section **********************************


|****************************** choice section ********************************

Mike,

Where are the fields on the form? Depending on where you place the code you may not need a display statement at all or just display the actual field(display("someform.field")).

Hi Mark, sorry for taking so long to reply. I have attached the form layout and marked the fields that am I am trying to return the info to. They are single occurance fields.

Mike

Hi Mark, have included the code as below, the following

|display.all() | Display fields on form.

If not commented out results in a recursion error, please see the attached.

Thanks,

Mike


|******************************************************************************
|* ticsto500 0 VRC B40C c test
|* Display Material Quantities Issued Per P/O No
|* Stuart Orr
|* 1998-06-24
|******************************************************************************
|* Main table ticst001 Estimated and Actual Material Costs, Form Type 3
|******************************************************************************

|****************************** declaration section ***************************
declaration:

table tticst001 | Estimated and Actual Material Costs
table ttisfc001 | Production Orders
table ttcmcs003 | Warehouses
table ttiitm001 | Item Master
table ttccom010 | Customer Master

field.tisfc001.mitm:
before.display:

SELECT tiitm001.dsca, tiitm001.cuno, tccom010.nama
FROM tiitm001
WHERE tiitm001._index1 = {:tisfc001.mitm}
and tccom010.cuno = tiitm001.cuno
as set with 1 rows
SELECTDO
display("tiitm001.dsca")
display("tiitm001.cuno")
display("tccom010.nama")
refresh()
ENDSELECT
|display.all() | Display fields on form.

|****************************** form section **********************************


|****************************** choice section ********************************

field.tisfc001.mitm:
before.display:

SELECT tiitm001.dsca, tiitm001.cuno, tccom010.nama
FROM tiitm001
WHERE tiitm001._index1 = {:tisfc001.mitm}
and tccom010.cuno = tiitm001.cuno
as set with 1 rows
SELECTDO
display("tiitm001.dsca")
display("tiitm001.cuno")
display("tccom010.nama")
refresh()
ENDSELECT

Hi Bernd, The select is working great as per the attached, still having no luck with the display going back to the form.

Mike

Hi Bernd, The select is working great as per the attached, still having no luck with the display going back to the form.

Mike

Hello Mike,

define variables an use them on the form like :

extern domain tcdsca mitm.dsca
extern donain tcnama cust.nama

field.tisfc001.mitm:
before.display:

SELECT tiitm001.dsca, tiitm001.cuno, tccom010.nama
FROM tiitm001
WHERE tiitm001._index1 = {:tisfc001.mitm}
and tccom010.cuno = tiitm001.cuno
as set with 1 rows
SELECTDO

mitm.dsca = tiitm001.dsca
cust.nama = tccom010.nama

SELECTEMPTY

mitm.dsca = "xx"
cust.nama = "xx"

ENDSELECT

display("mitm.dsca")
display("tiitm001.cuno")
display("cust.nama")
refresh()


Put the 3 fields on form as display.fields

So if there's no customer, xx is displayed , or use "" for nothing displayed.

Hope it helps

Regards

Hi Bernd, the variables worked!! Deleted tiitm001.dsca and readded it as for some reason it simply would not work when i renamed the form field to mitm.dsca. Have also taken out the display statements as well. Thanks for all the help to you and Mark.

Mike