Hi,
I ask if there is a method to insert values of an existing table into a new table that don't have values. I see that there is db.update, but I don't know how to use for this specific use.
My purpose is to display this new table in a report, because I don't need of all the fields of the existing table but only some that I have already put.
Regards,
RavCoder

Well, you can do just that. (Take field values of table A. Assign these values to fields of table B. Insert the fields as a new dataset into table B. Print the values of table B. Delete the new inserted dataset of table B, because you don't need the new dataset any longer?) It will work, just mind the index fields, just in case you don’t want to get a duplicate value error on insert.

But I still prefer the direct approach of printing the fields of table A directly on the report.
If you want to modify data prior to printing, it can be done in the before.layout section of the report script.

i.E. the report is printing tcibd001.item in layout detail.1. You can change the value of tcibd001.item in the before.layout section:


detail.1:
before.layout:
tcibd001.item = “ bunny ears”

Hi,
I didn't understand exactly how to do it, but I did this:



function insert_field_table(){
db.retry.point()
select tdsls900.* , tdsls400.ofbp, tdsls400.odat, tccom100.nama,
tdsls401.orno, tdsls401.cups, tdsls401.item , tdsls401.qoor
from
tdsls900, tdsls401, tdsls400, tccom100
where tdsls400.ofbp = tdsls401.ofbp
and tdsls400.orno = tdsls900.orno
and tccom100.bpid = tdsls900.bpid

selectdo
tdsls400.ofbp= :tdsls900.bpid
tccom100.nama = :tdsls900.nama
tdsls401.item= :tdsls900.item
tdsls401.cups = :tdsls900.cuni
tdsls400.odat = :tdsls900.odat
tdsls401.orno = :tdsls900.orno
tdsls401.qoor = :tdsls900.qoor
db.retry.point()

db.insert(ttdsls900, db.retry)
commit.transaction()



rprt_send()
endselect


}


However I only print certain values ​​(which are not present here but in other functions).
I've been stuck on this for a long time, because I don't know exactly how to do it.
Regards,
RavCoder

OK, let's update a table. We will only update one field in a single dataset:


db.retry.point()
select tdsls400.*
from tdsls400 for update
where tdsls400._index1 = {:tdsls900.orno}
|tdsls400.orno = :tdsls900.orno |this is equal to the line above, because tdsls400.orno is the index
selectdo
tdsls400.odat = tdsls900.odat
db.update(ttdsls400, db.retry)
commit.transaction()
endselect


You’ll need a where-clause to specify the datasets you want to change. Values taken from “outside” of the select will be starting with “:”.
So :tdsls900.orno is a variable (the value of the field tdsls900.orno, but not the field itself).
In selectdo a new value is assigned to the specified field. The value of tdsls900.odat is assigned to tdsls400.odat. No need to use a “:” here. The compiler will handle fields as variables here.
To write this change into the database you need to perform an update. db.update(..) will do just that (or db.insert(..) or db.delete()..)), well – kind of – we need to talk transactions first.
A transaction is a bunch of changes in the database. You’ll want to write these changes together in the database, or – in case of an error – you do not want to update at all (this is called “rollback” – throwing away all changes in case of an error).
A transaction starts with a retry point. Updates will follow the retry point and will be “closed” by a commit-statement. (commit.transaction()). In case of an error, when writing data into the database, LN will perform a rollback and return to the retry point.
This is one way to modifying a dataset. This is the old BaanIV style. In LN you can also use DAL functions to modify or create a dataset. DAL functions will call DAL hooks in the background. These hooks can modify fields when needed.

But in your example, there’s no need to store the tdsls900 fields in the other tables. You can just print the tdsls900 fields in the report.

Thanks, but I tried your code and gave me an error:

error 206 : Record is not locked on tdsls900121 in db.update

Did you make sure your code has "from tdsls400 for update".

Thanks, but I tried your code and gave me an error:

error 206 : Record is not locked on tdsls900121 in db.update
Please post your code. In my code there's no update of table tdsls900.

Hi,
this is my code:

function insert_field_table(){
db.retry.point()
select tdsls900.* , tdsls400.ofbp, tdsls400.odat, tccom100.nama,
tdsls401.orno, tdsls401.cups, tdsls401.item , tdsls401.qoor
from
tdsls900, tdsls401, tdsls400, tccom100 for update
where tdsls900.bpid = tdsls400.ofbp
and tdsls900.nama = tccom100.nama
and tdsls900.item = tdsls401.item
and tdsls900.cuni = tdsls401.cups
and tdsls900.odat = tdsls400.odat
and tdsls900.orno = tdsls401.orno
and tdsls900.qoor = tdsls401.qoor
selectdo



db.update(ttdsls900, db.retry)
commit.transaction()



rprt_send()
endselect


}



This function is into function section :

|****************************** function section ******************************

functions:

function read.main.table()
{
select tdsls400.orno, tdsls401.item, tdsls401.ofbp, tdsls401.odat
from tdsls400, tdsls401

where tdsls400._index1 inrange {:orno.f}
and {:orno.t}
and tdsls401.orno = tdsls400.orno
group by tdsls400.orno , tdsls401.item, tdsls401.ofbp , tdsls401.odat





selectdo
sum_period()
sum_period()
insert_field_table()
rprt_send()
endselect
}



My purpose is to insert the existing table values ​​into a new empty table I created.
Regards,
RavCoder

from tdsls900, tdsls401, tdsls400, tccom100 for update

Please add "for update" to the table you want tu update. You're telling LN to update table tccom100 but not table tdsls900.

So, try this:

from tdsls900 for update, tdsls401, tdsls400, tccom100

And add a ":" in the where clause:

where tdsls900.bpid = :tdsls400.ofbp

Now LN will take the value of field tdsls400.ofbp selected in read.main.table().

But you don't insert a dataset in table tdsls900, you're are updating an existing dataset. And your Code won't compile nor will it work the way you want it to work (I think).

I’ll try to explain how a select statement in LN/Baan works:
The basic SELECT structure is like this:

SELECT
- (from/where)
SELECTDO
- code to modify found data
SELECTEMPTY
- code to insert new data
ENDSELECT
SELECT is used to define/find the data of one or more tables. You cannot modify or add data in the SELECT section. This is a huge difference to other SQL-languages.
Modifying or adding new data will require code in the SELECTDO and/or SELECTEMPTY section.

Hint: this a verry simplified example!


db.retry.point()
select tdsls900.*
from tdsls900 for update
where tdsls900.bpid = :tdsls400.ofbp
selectdo
|Code will be executed when dataset is found in tdsls900.
selectempty
|This part will be executed if the select did not find any data.
|So that’s the part where you insert a new dataset into the table.

|OK, lets start with initialising the fields of tdsls900:
db.set.to.default(ttdsls900)
|The field are now empty.

|No fill the fields with data:
tdsls900.bpid = tdsls400.ofbp
tdsls900.nama = tccom100.nama
tdsls900.item = tdsls401.item
tdsls900.cuni = tdsls401.cups
tdsls900.odat = tdsls400.odat
tdsls900.orno = tdsls401.orno
tdsls900.qoor = tdsls401.qoor
|Now the data for the new datasat is set. Lets insert:
db.insert(ttdsls900, db.retry)
commit.transaction()
endselect


You will find tdsls900.nama to be empty after inserting. That’s because tccom100 is not read yet. You will need to add a select on tccom100, too.

Btw: my code is untested, and I have not compiled it.

Hi,
Thanks, the error is gone, but I still don't see my data and it takes a lot to load the report.
Regards,
RavCoder

Is the missing data stored in tdsls900? Or do you have empty fields in the created datasets?

Hi,
I don't understand exactly you mean, but the table tdsls900 is a empty table that I created ,while the others table were created yet by other.
I thought that used db.update and then used db.insert should work. I used also your code, but nothing change.
Regards,
RavCoder

Check the data flow from select in session script to report. For example: are the input fields set in the report and filled before rprt_send? The layout section is triggert and the print expression is ok?

I think that I put input field correctly and insert my session script:

|******************************************************************************
|* tdsls4400 0 VRC B61U 10 stnd
|* Visualizza ordini bp
|* Installation User
|* 2017-05-17
|******************************************************************************
|* Main table tdsls400 Ordini di vendita, Form Type 4
|******************************************************************************

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

table ttdsls400 | Ordini di vendita
table ttdsls401 | Righe ordini di vendita
table ttdsls900 | Nuova tabella che prende i record della tabella tdsls401
table ttccom100
extern domain tcorno orno.f fixed
extern domain tcorno orno.t fixed
extern domain tcyesno txta.txt
extern domain tcyesno txtb.txt
extern string tdsls900.bpid
extern string tdsls900.nama
extern string tdsls900.item
extern string tdsls900.cuni
extern long tdsls900.odat
extern string tdsls900.orno
extern double tdsls900.qoor



extern domain tdsmo somma
extern domain tdqper quantita_periodo



|****************************** program section ********************************






|****************************** group section **********************************

group.1:
init.group:
get.screen.defaults()




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

choice.cont.process:
on.choice:
execute(print.data)

choice.print.data:
on.choice:
if rprt_open() then
read.main.table()


sum_order()
sum_period()

insert_field_table()

rprt_close()
else
choice.again()
endif


|****************************** field section *********************************

field.orno.f:
when.field.changes:
orno.t = orno.f






|****************************** function section ******************************

functions:

function read.main.table()
{
select tdsls400.orno, tdsls401.item, tdsls401.ofbp, tdsls401.odat
from tdsls400, tdsls401

where tdsls400._index1 inrange {:orno.f}
and {:orno.t}
and tdsls401.orno = tdsls400.orno
group by tdsls400.orno , tdsls401.item, tdsls401.ofbp , tdsls401.odat





selectdo
sum_period()
sum_period()
insert_field_table()
rprt_send()
endselect
}





function sum_order() {
db.retry.point()
somma = 0
select tdsls401.ofbp,tdsls401.item
from tdsls401

group by tdsls401.ofbp,tdsls401.item

selectdo
select tdsls401.qoor
from tdsls401
where tdsls401.item = :tdsls401.item
and tdsls401.ofbp = :tdsls401.ofbp

selectdo
somma = somma + tdsls401.qoor


endselect

rprt_send()
endselect




}


function sum_period()
{
db.retry.point()
quantita_periodo = 0
select tdsls401.ofbp, tdsls401.odat
from tdsls401
group by tdsls401.ofbp, tdsls401.odat
selectdo
select tdsls401.qoor
from tdsls401
where tdsls401.ofbp = :tdsls401.ofbp
and tdsls401.odat = :tdsls401.odat

selectdo
quantita_periodo = quantita_periodo + tdsls401.qoor
endselect
rprt_send()

endselect
}


function insert_field_table(){
db.retry.point()
select tdsls900.*
from tdsls900 for update
where tdsls900.bpid = :tdsls400.ofbp
selectdo

selectempty
tdsls900.bpid = ""
tdsls900.nama = ""
tdsls900.item =""
tdsls900.cuni = ""
tdsls900.odat = 0
tdsls900.orno = ""
tdsls900.qoor = 0
db.set.to.default(ttdsls900)



tdsls900.bpid = tdsls400.ofbp
tdsls900.nama = tccom100.nama
tdsls900.item = tdsls401.item
tdsls900.cuni = tdsls401.cups
tdsls900.odat = tdsls400.odat
tdsls900.orno = tdsls401.orno
tdsls900.qoor = tdsls401.qoor

db.insert(ttdsls900, db.retry)
commit.transaction()
endselect


}



If there were other things I have to add or show, write well and I'll try to put (I'm inexperienced on this technology, for me it's all new)

table ttdsls900 | Nuova tabella che prende i record della tabella tdsls401
(..)
extern string tdsls900.bpid
extern string tdsls900.nama
extern string tdsls900.item
extern string tdsls900.cuni
extern long tdsls900.odat
extern string tdsls900.orno
extern double tdsls900.qoor

Why did you do that? What's it supposed to do?
If a table is declared, the fields are automatically declared too.
I've never seen something like this in a LN script before.

You're sending data three times to the report.


function read.main.table()
{
select tdsls400.orno, tdsls401.item, tdsls401.ofbp, tdsls401.odat
from tdsls400, tdsls401
(..)
selectdo
sum_period() |(-> function uses rprt_send())
sum_period() |(-> function uses rprt_send())
insert_field_table()
rprt_send() |3 times rprt_send() in one select
endselect
}

table ttdsls900 | Nuova tabella che prende i record della tabella tdsls401
(..)
extern string tdsls900.bpid
extern string tdsls900.nama
extern string tdsls900.item
extern string tdsls900.cuni
extern long tdsls900.odat
extern string tdsls900.orno
extern double tdsls900.qoor

Why did you do that? What's it supposed to do?
If a table is declared, the fields are automatically declared too.
I've never seen something like this in a LN script before.

You're sending data three times to the report.


function read.main.table()
{
select tdsls400.orno, tdsls401.item, tdsls401.ofbp, tdsls401.odat
from tdsls400, tdsls401
(..)
selectdo
sum_period() |(-> function uses rprt_send())
sum_period() |(-> function uses rprt_send())
insert_field_table()
rprt_send() |3 times rprt_send() in one select
endselect
}

to answer your question: I don't really know why I declared the external variables again, I thought it was a way to see them.
As for the fact of having used rprt_send three times was that if I do not add it to my methods it does not send anything to the report.
Moreover, as I have already said in other posts on this platform, I am not an LN expert and there are no sufficient sources, not even from other parts, except for the documentation that I just can't understand.
Not even so exactly which paradigm uses LN.Imperative, procedural, object-oriented?
What language do you approach? Java? Pascal? (which I've never seen)
So forgive me if I make these mistakes or when you say certain things, I honestly don't understand them.
I apologize again.
Regards,
RavCoder

I always considered Baan 3GL/4GL to be a wild mixture of Basic and Pascal. It’s old school 1980th procedural mixed with Dynamic SQL.
When Infor introduced LN with DAL-Hooks, it may have been the right time to take the turn to a more modern object-orientated highway, but they didn’t.

This may have changed with the introduction of “extensions”, but I doubt Infor will label it “object-orientated”. (I still haven’t worked with extensions. But this will change shortly, Training is on the way..)

I think, you do now the documentation of solution KB 22924522. There’s also a “Infor LN Development Tools Development Guide“ available in the Tools section in the documentation browser of Infor Support. It is available for all releases of Infor LN. You may want to dig into “To create reports” and “Overview of Software Components/Reports”. This chapters will provide a rough overview on how to create printing sessions. (I’m into a version called U8883L US for Infor LN 10.2.1 right now.)

In the old days there’s been training provided by Infor called “Application Administration” (AA) and “Application Customisation” (AC) – the names may have changed in other countries. Maybe you can find old training documentation; it may come in handy too.

I've already tried to see if there were resources on the Internet except the one you say, but they are scarce and I really don't understand them.
I tried to delete the things you said were additional, but still don't let me see the data.
I have also tried to debug and I have seen that it does not even insert the values, rather it skips the execution of my function.