Have you tried to pull with Excel or Access or Crystal and use a date parameter?

Thanks BL. With apologies to Harvey Korman (aka Hedly Lamar), I was going to "head you off at the pass" by saying that I've already got the VI job running on a batch file and tied to a windows task scheduler task - so really I am kind of tied to the VI solution. 

Unless... Is there a way to tie an Excel or Crystal export to a batch file and connect it to a windows task? Without a 3rd party app like KnowledgeSync or Visual Cut?

Tying an Excel to a batch file and opening it through Task Scheduler is an easy task, if you need the Excel file to do something just tie a macro to it and have it refresh when it opens.

Thanks, I appreciate the Excel ideas, but what I really want and need is an argument to put into the selection criteria for the DateChanged field in VI. This has to be possible, doesn't it? I even think I've seen it somewhere before. 

I tried to replicate what you say you are doing with the VI. In my Sage 100 2022 version, I am not able to add any field numbered above 97 (Sage DataFile Layout documentation) to the export job from export job maintenance. DateCreated and DateUpdated fields are both above that number. These fields do not seem to be exposed in the VI export maintenance. Maybe I am missing something? They are exposed in the ODBC data, so like recommended by BigLouie earlier, Excel and Access should work to look for today's updated records.

I was able to retrieve the data very quickly in the system I have set up. I think this is the cat's meow and I would like to thank Kevin M for his explanations of this in these forums.

For anyone interested in my setup:

ODBC connection established in my Data Sources.

Downloaded and installed MS SQL Server Express.

Make a linked table to Sage using ODBC.

Draw data into a database table.

Query against that table using webpages in PHP or Crystal Reports or SQL Studio.

I do not seem to be able to query against the linked table directly, so I pull data into a new database table. I run a SQL query against that. I have the first commands in my queries set up to drop and then redraw the table so I have up to date info (comment it out if using the same table over and over). I have a linux server in my domain, and I have some knowledge with that, so I have CRON job set up to automatically redraw info overnight for stuff that is not up to the minute critical (I am sure you could do this with MS built in stuff too if you have expertise there). I have some webpages that draw from those data tables. My warehouse crew can then look up bin locations without having to come to Sage.

My purchasing manager can pull a crystal report against that data that works in just a minute or two. I worked with Mike McNellis who set up user-defined tables and a script to conglomerate this data within Sage (a lot of big tables with lots of connections to other tables). His excellent work made it possible to do the report inside Sage, though it took hours to run. I had to set the script to draw the data to start at 11 PM so they could use the report at 8 am if nothing screwed it up. With SQL and the ODBC, I can update this in a lot less time (minutes), and I still set it off with a CRON job overnight so the data is ready to query when people arrive to work.

My biggest regret with SAGE implementation is not having known how to do this ODBC/SQL Linked Table when we started. If I were selling Sage, I would make sure each client had this setup offered to them so they could pull data easily and quickly. The thing in Sage that covered my needs earlier was GetX from XKZero. That app was a lifesaver for finding things in Sage and is on my highly recommended list. If I dropped this app, I might not show up at work for fear of experiencing some hurt.

The MSSQL command that got the DateUpdated of today:

SELECT ItemCode, DateUpdated, UserUpdatedKey, TimeUpdated
FROM CI_Item
WHERE CONVERT(varchar(10),DateUpdated,120)=CONVERT(varchar(10), getdate(),120)

--The CONVERT command just makes sure the dates are in the same format so they can be compared.

For what it's worth, hopefully, it helps someone,

Chris

Thanks Beevet - that's some great stuff right there. I may work on doing something like that if I can't get this resolved in VI.

For your VI question, I think all you need to do is check this box in Options:

I may be missing something, as I don't really understand your reference to the numbered fields, but this gives me these fields:

RCLOWE,  Thanks for pointing out the read only check box.  That was part of my missing understanding of what you are doing.  I was able to replicate the issue you are having.  

I think I made progress on this, but am not there.

First, the numbers I referenced were the numbers next to the fields in Data File Display and Maintenance or Data File Lay Out documentation from Sage.

Next, date fields seem extremely complicated as the date can be stored and displayed in a number of ways.  The date I see in its raw form is YYYYMMDD.  When I look at your formulas, I added the formatting rule to this formula to make the strings match: 

DTE(DAY:"%Y%Mz%Dz")

The semicolon separates the formatting definition from the function DAY which pulls today's date.  The z after M and D forces them to two digits so 8 becomes 08 to match Sage format.  

I put this in a Temp field in the Data tab of the export. 

It seems to use a Select on the next tab, you have to have a second Data of your field in question in which you Calculate the value.  You cannot select on the Tempxxx fields.  So, I had two DateUpdated entries on the Data tab.  The first was the one that comes in normally and the second had changed the Operation to Calculated and then entered the calculation field.  The calculation I used was this: 

TBL(CI_Item01.DateUpdated$=Temp004$,"Not Today","Updated Today")

This is a Table Function from ProvidexPlus (https://manual.pvxplus.com/PXPLUS/functions/tbl.htm) where in one of the examples they say this function basically works as a logical operator if you consider:

In effect, this form of TBL( ) becomes TBL(logical_expr,else_value,true_value).

In my export, I then had a field that was showing "Not Today" for most records and "Updated Today" on the one record I did change today. 

I followed David Speck's write up in this post to get that far: 

www.sagecity.com/.../455729

I cannot get the Select to work however.  I am not sure what I am doing wrong.  I tried other fields instead of the DateUpdated field.  I tried no spaces, upper space conversion, other words, and True/False but did not get it to work. 

Maybe you have more experience and knowledge and can get it from here? 

I did read a comment by Kevin M who said basically data exports should be done with ODBC and not VI.  I see why he would say this after playing with this for a bit. 

Chris

Wanted to see if this can be done using Sage Intelligence.

Was able to create a report and have it run using the Windows Task Scheduler.

The report uses Today's Date as a filter on the records DateUpdated. Also, Item Type = 1

Set the Task Scheduler to run before 12:00AM and it pulled the correct items.

The report will generate the output file and save in a shared folder.

Report Before Today's Date filter:

After filter

If anyone wants to know, I finally consulted the master and found out that my first entry was correct, I just didn't quite have the correct syntax.

I had posted EVS("DTE(DAY)") and said that it didn't work. 

Well, I just needed an equal sign at the beginning of the line. Sometimes I tend to overlook the obvious.

Now, if only I could pull the Company Code into a Temp field I would be flying high today