Baanboard.com

Go Back   Baanboard.com > Forum > Baan Quick Support: Functional & Technical > Tools Development

User login

Frontpage Sponsor

Main

Poll
For ERP LN feature pack upgrade, what method of install are you using?
Installation Wizard into existing VRC
35%
Installation Wizard into new VRC
42%
Manual into existing VRC
3%
Manual into new VRC
19%
Total votes: 31

Baanboard at LinkedIn


Reference Content

Reply
 
Thread Tools Display Modes
  #1  
Old 14th November 2016, 20:52
lotius81 lotius81 is offline
Junior Member
 
Join Date: Nov 2016
Posts: 25
lotius81 is on a distinguished road
Baan: Infor LN 10.4 - DB: SQL Server - OS: Windows Server 2008
Sorting by production start date
Baan: ERP LN 6.1 FP10 (Infor ERP 10.4)
C/S: None/Unknown

Hey all,

LN newcomer here. I'm putting together a report for production management. It sorts by planned start date, and also uses an after.field based on planned start date to show a subtotal ("Total" aggregate function) of planned production for a given date.

The issue I'm running into is that every single line shows the subtotal. My guess is that because planned start date is stored as both date and time, that it is seeing the different times within a date and thinking it needs to show the subtotal every time. Is there a way (function or otherwise) in my after.field to strip down the planned start date and time field to only contain the mm-dd-yyy information so that this subtotals correctly? Or am I looking in the wrong direction?

Much thanks,
Rich
Reply With Quote
  #2  
Old 15th November 2016, 13:08
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Just search this forum for UTC Dates. There are a bunch of threads on converting UTC date to a date. This is just one such thread http://www.baanboard.com/baanboard/s...ight=UTC+Dates. From the threads I saw I would probably change the input field to the report to date field, then in the session script convert the UTC date to date using sprintf, then pass that to the report to be used. Not a LN user so wait for one of them for more suggestions.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
Sponsored Links
  #3  
Old 16th November 2016, 18:06
lotius81 lotius81 is offline
Junior Member
 
Join Date: Nov 2016
Posts: 25
lotius81 is on a distinguished road
Baan: Infor LN 10.4 - DB: SQL Server - OS: Windows Server 2008
Strange. Every time I try to do any kind of conversion on the date values I end up with empty variables.

Searched around and found some code I thought would work perfectly...

Code:
before.field.tisfc001.prdt.1:
before.layout:
   long  ret, o.year, o.mnth, o.dayn, o.hr, o.mn, o.sc

   ret = utc.to.date(tisfc001.prdt, o.year, o.mnth, o.dayn, o.hr, o.mn, o.sc)
   date.without.time = date.to.num(o.year, o.mnth, o.dayn)
"date.without.time" is an input field of domain tcmcs.date.

Then on my after.field layout I try to output date.without.time. Nothing shows.

Maybe I'm not putting the code in the right spot in the script?
Reply With Quote
  #4  
Old 16th November 2016, 19:28
bdittmar's Avatar
bdittmar bdittmar is offline
Guru
 
Join Date: Apr 2002
Location: Germany, 50.584097,8.544078
Posts: 1,639
bdittmar will become famous soon enough
Baan: 2.2/3.1/4c4/LN6.1 FP6/FP9/HiDox - DB: tbase, ms-sql7, oracle10gV1, 11g - OS: HP-UX, W2K3, SLES
sprintf$()

Quote:
Originally Posted by lotius81 View Post
Strange. Every time I try to do any kind of conversion on the date values I end up with empty variables.

Searched around and found some code I thought would work perfectly...

Code:
before.field.tisfc001.prdt.1:
before.layout:
   long  ret, o.year, o.mnth, o.dayn, o.hr, o.mn, o.sc

   ret = utc.to.date(tisfc001.prdt, o.year, o.mnth, o.dayn, o.hr, o.mn, o.sc)
   date.without.time = date.to.num(o.year, o.mnth, o.dayn)
"date.without.time" is an input field of domain tcmcs.date.

Then on my after.field layout I try to output date.without.time. Nothing shows.

Maybe I'm not putting the code in the right spot in the script?
Hello,
use sprintf$() function like :
Code:
string date.without.time(10)
date.without.time = sprintf$("%D(%02m-%02d-%04Y)", tisfc001.prdt)
Should give a string as mm-dd-yyyy

For sorting a format like yyyymmdd is:

date.without.time = sprintf$("%D(%04Y%02m%02d)", tisfc001.prdt)

or
tc.year, tc.week, tc.month

year = val(sprintf$("%u(%04Y)",tisfc001.prdt))
week = val(sprintf$("%u(%02W)",tisfc001.prdt))
month = val(sprintf$("%u(%02m)",tisfc001.prdt))


Regards
__________________
//Bernd

Last edited by bdittmar : 16th November 2016 at 19:38. Reason: yyyymmdd added
Reply With Quote
  #5  
Old 16th November 2016, 20:53
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Debug it and make sure date.without.time is getting set correctly. I also make sure I declare the variable as extern in the report script(I don't think you want it on the input fields of the report). And if you want this report sorted with this field then you need to run this in the session script and send date.without.time to the report.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
  #6  
Old 17th November 2016, 05:55
Ajesh's Avatar
Ajesh Ajesh is offline
Guru
 
Join Date: Feb 2009
Posts: 444
Ajesh is on a distinguished road
Baan: LN 10.4 - DB: Oracle - OS: HP-Unix
What needs to be done is that in your program script, just before you send record to printer using rprt_send().

You need to divide your Planned date UTC into Planned date by this...

Code:
Syntax:
function long utc.to.local  (long utc, ref long local_days, ref long local_time [, ref string local_dst])

Description

This converts a UTC long format value to local date and time. 


Arguments
long utc The UTC long format value.
 
ref long local_days The local date as the number of days since 01-01-0001. 
 
ref long local_time The local time as the number of seconds since 00:00 hour. 
 
[ref string local_dst]  DST_ON: The number of seconds in local_time should be considered as Daylight Saving Time. 

DST_OFF: The number of seconds in local_time should not be considered as Daylight Saving Time. 

DST_ON and DST_OFF defines are automatically available. Size of local_dst should be 7 chars. 

long date.without.time,time 

utc.to.local(tisfc001.prdt,date.without.time,time)
Reply With Quote
  #7  
Old 17th November 2016, 15:24
lotius81 lotius81 is offline
Junior Member
 
Join Date: Nov 2016
Posts: 25
lotius81 is on a distinguished road
Baan: Infor LN 10.4 - DB: SQL Server - OS: Windows Server 2008
I tried your suggestion. The output of my values is still 0 for everything. As you can see on the attachment, for each row I'm printing out the actual tisfc001.prdt value, and on the next line the 3 "0.00" values are month, day, year values from the sprintf(). I can't figure out why the values aren't being passed along to the report.
Attached Images
File Type: png empty_values.png (7.5 KB, 16 views)
Reply With Quote
  #8  
Old 17th November 2016, 23:57
lotius81 lotius81 is offline
Junior Member
 
Join Date: Nov 2016
Posts: 25
lotius81 is on a distinguished road
Baan: Infor LN 10.4 - DB: SQL Server - OS: Windows Server 2008
I guess I don't have access to the session script.
Reply With Quote
  #9  
Old 18th November 2016, 06:33
günther günther is offline
Guru
 
Join Date: Jan 2002
Location: Ehingen, Germany
Posts: 571
günther is on a distinguished road
Baan: IVc4 - DB: Informix - OS: HP-UX
Hi Rich.

If have access to the program script, please check the following: year, weak, month and so on must be declared as "extern" so that they can be accessed by the report script (and they must be Input fields in your report, but that must be true to get them sorted).

If you don't have acces to the program script, you still can do that. Have a look at my pretty old thread [url="http://www.baanboard.com/baanboard/showthread.php?t=9090&highlight=Advanced+Report"]. This will enable you to set year, weak, month and so on as input fields (and sort by them) even if they can't be set by your program script -- it's up to you to set them in your Report script just before the sorting starts.

Regards
Günther
Reply With Quote
  #10  
Old 18th November 2016, 07:16
Ajesh's Avatar
Ajesh Ajesh is offline
Guru
 
Join Date: Feb 2009
Posts: 444
Ajesh is on a distinguished road
Baan: LN 10.4 - DB: Oracle - OS: HP-Unix
The Report writer takes all the records from the Program Script, sorts the records according to the input field and then displays the record to the OP. It doesnt take all the records and then sorts it while the layouts are getting displayed simultaneously. It doesnt work that way..

What you could do is define a field like report.sort and declare it as a boolean. declare your after.field.report.sort, and the sub totals in that Layout. While in the detail section extract the date field from UTC and compare it with previous value, and if it is changed, change the value of the report.sort to true and then your Sub Totals will get displayed.

Only thing is that you will need to supress the conditions where this after.field will get displayed where you dont want it. You would need to find that out.

Also, the other thing would be when you would compare it with previous value sometimes you would get the same value even though it is a different record. This happens between the complex data interchange between PS and RW.
Reply With Quote
  #11  
Old 18th November 2016, 22:29
lotius81 lotius81 is offline
Junior Member
 
Join Date: Nov 2016
Posts: 25
lotius81 is on a distinguished road
Baan: Infor LN 10.4 - DB: SQL Server - OS: Windows Server 2008
Progress! I'm able to define an input field, set its value in the report script, and sort by it. I did that by using the function from Gunther's post. Now, in theory I should be able to make an after.field for my sorted input field and do the Total aggregate on that, right? And that should trigger my subtotal line.

What's happening, though, is the very first line is a subtotal line with a subtotal value of 0 (picture attached). My input sort fields sequence is tisfc010.mcno (machine), then k.of.data.sfc010, and then new.concat.date (new date in format of yyyymmdd). Seems to be sorting correctly. Then I have an after.field layout for new.concat.date where the Total aggregate should give me a total of planned production for that date. I assume it gets triggered when new.concat.date changes, but apparently that isn't how it works.

Following Gunther's advice at the bottom of my report script I have:

Code:
#undef  r.write.seq.file()

function extern r.write.seq.file()
{
    new.concat.date = val(sprintf$("%u(%04Y%02m%02d)",tisfc010.prdt))
    ORIGINAL.FUNCTION()
}
and the appropriate declarations at the top per his link to his older post. That's where the 3rd sequence part of my sorted input gets assigned. I'm possibly doing something wrong in the layouts listing.

And just as an FYI I appreciate the help you guys are offering up. It's helping me learn a lot very quickly.
Reply With Quote
  #12  
Old 20th November 2016, 07:28
vamsi_gujjula's Avatar
vamsi_gujjula vamsi_gujjula is offline
Senior Member
 
Join Date: Nov 2011
Location: hyderabad ; india
Posts: 240
vamsi_gujjula is on a distinguished road
Baan: ERPLN fp3 and 10.4 - DB: oracle - OS: Aix , Redhat linux 6
did you miss the macros ?
#define ORIGINAL.FUNCTION() originally.generated.r.write.seq.file()
#define r.write.seq.file() ORIGINAL.FUNCTION()
__________________
Regards,
Vamsi
Reply With Quote
  #13  
Old 21st November 2016, 18:13
lotius81 lotius81 is offline
Junior Member
 
Join Date: Nov 2016
Posts: 25
lotius81 is on a distinguished road
Baan: Infor LN 10.4 - DB: SQL Server - OS: Windows Server 2008
Nope, I have them in the declarations. I just didn't write it out here. Sorry about that.

Making more progress (pictured). I wasn't able to use the Total aggregate function, so I'm manually accumulating into a variable on each row until I hit the after.field. I put in OLDDATE: and NEWDATE: to try and see what values are being looked at in each row.

One of my primary trip-ups in this whole process is understanding the flow of when certain variables are being assigned, processed, etc. Is there a flowchart, good documentation (I feel like I've looked everywhere) or something so I can get a better handle on what is happening when?
Attached Images
File Type: png subtotals_incorrect_2.png (71.4 KB, 9 views)
Reply With Quote
  #14  
Old 21st November 2016, 20:00
lotius81 lotius81 is offline
Junior Member
 
Join Date: Nov 2016
Posts: 25
lotius81 is on a distinguished road
Baan: Infor LN 10.4 - DB: SQL Server - OS: Windows Server 2008
Nevermind, I found a reference and read through the whole thing multiple times. Figured out my problem. Thanks everyone for your help!
Reply With Quote
  #15  
Old 22nd November 2016, 15:03
mark_h's Avatar
mark_h mark_h is offline
Guru
 
Join Date: Sep 2001
Location: Kentucky, USA
Posts: 6,905
mark_h will become famous soon enough
Baan: Baan 4C4 A&D1 - DB: Oracle - OS: Sun Solaris
Can you tell us what the final solution is? Might help someone in the future.
__________________
Mark

GO Cards!
My latest mantra - make sure you have latest stpapi patches and the latest session object. If on LN then please explore the option of using DAL2 functionality.

Shared Solutions for Baan systems provided free by Baan Board.
Play the Google game and help Baanboard get better rankings. Do your part. Click here to find how.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Production Start Date Joy Conner Manufacturing & Supply Chain 2 27th September 2012 15:20
Production Date on a Production Order cherokee Manufacturing & Supply Chain 0 19th November 2004 17:26
Measuring Original MRP Start Date on SFC order Bryan Manufacturing & Supply Chain 3 3rd September 2004 12:48


All times are GMT +2. The time now is 12:12.


©2001-2017 - Baanboard.com - Baanforums.com