Spreadsheet designer not completing reports

I'm trying to run a query to look at req lines, showing item type, and I would like to see the GL account since January.

I tried running using RequisitionLine, but when I use the CreateDate (greater than or equal to) as the filter it runs forever (upwards of 2 hours) and I end up having to kill it. Also, this does not give me the GL Account.

In order to get the GL Account I need to use RequisitionLineDistribution and pull the GL Account (I forget the exact field name) but in order to limit the amount of data that gets returned I have to link to Requisition or RequisitionLine and use CreateDate again, but the filter capabilities through the link only gives me the option to use Begins With, Ends with, or equals. I tried Begins With 2019 but that returns no records. So, I end up running it wide open for all dates and of course, it never stops running because its trying to return every req line ever made.

I'm fairly new to spreadsheet designer so guidance would be greatly appreciated.

com_2010feb_proactive.pdf

Find more posts tagged with

Infor Lawson Technology Group - Discussion

Comments

don-peterson

The date format for queries is yyyymmdd (noted at the bottom of the filtering page in the Help text). Also, check which version you are using. Newer versions have the ability to limit the number of records coming back by checking a box below the business class once the query is inserted into the worksheet and then entering a number.

unknown

I've been using the format you mentioned for a few months now, but very recently now I can't get it to filter by any dates, in any format. I even went as far as to copy a date from a report and paste it into a query. It still gives me an error, stating I have entered the date incorrectly.

I want to pull data for the past 12 months, so today I was entering >=20191022. I get an error stating:

The following does not comply with the valid formats: 20191022

valid formats:

dddd, dd MMMM yyyy HH:mm:ss.ff tt

M/d/yyyy h:mm:ss.ff tt

so I copied, directly from a report: Friday, 19 October 2018 06:30:54.00 AM

If I change ANY part of that, even the time, it fails saying I have it entered in the wrong format again. Have you seen this in the past? did the formats change?

don-peterson

Here we go...

I just tried this and it worked:

Run the query for a few records including the CreationDate field

Copy one of the CreationDate values and paste it into Notepad

Edit the date/time string to be what you want

Copy that and use it in the filter builder.

The data I had showed a CreationDate of: Thursday, 15 June 2017 11:32:24.00 AM

I wanted to change it to the first of August 2017: Tuesday, 01 August 2017 00:00:00.00 AM

I then went to the filter builder in Spreadsheet Designer for the Creation Date field and pasted in the edited date/time.

The CreationDate field, even though a date field, is not a standard date field in that is ia timestamp.

See if this works on yours.

Don

Copyright © 2025 Infor. All rights reserved.