Not sure if this helps, but you can probably get the information from the SO_SalesOrderHistoryHeader file.  This file should have an invoice number field.  It will be blank if an invoice has not been created.

No, unfortunately that doesn't help. I don't need the invoice number, I need a constant value of Y or maybe like the date the invoice was created.

Well, I'd like to get into scripting but don't know where to begin.

Could anyone help me out with a simple script where IF the CurrentInvoiceNo field is > 1, it populates a UDF with a Y?

Column post validate on that field.

if value <> "" then

retVal = oBusObj.SetValue("UDF_YOURFIELDNAMEHERE$", "Y")

else

retVal = oBusObj.SetValue("UDF_YOURFIELDNAMEHERE$", "N")

end if

Unfortunately, a brief test on version 2018 resulted in neither the post validate of CurrentInvoiceNo or the pre write of SO_SalesOrderHeader events firing when invoicing a sales order.

As Kevin pointed out, if the CurrentInvoiceNo field is populated, it is currently being shipped/invoiced. You can use this in the sales order entry/inquiry lookup or any reports you might be running to determine whether or not an invoice exists. In the report, you can always translate it to "Yes" or "No".

Once it is posted and if you are retaining sales history, then you can refer to LastInvoiceDate and/or LastInvoiceNo fields in SO_SalesOrderHistoryHeader if you are using SO History vs AR Invoice History for orders that shipped complete. If the order was not shipped complete, then you can refer to LastInvoiceOrderDate and LastInvoiceOrderNo in SO_SalesOrderHeader.

You're saying that script won't work?

"As Kevin pointed out, if the CurrentInvoiceNo field is populated, it is currently being shipped/invoiced. You can use this in the sales order entry/inquiry lookup or any reports you might be running to determine whether or not an invoice exists. In the report, you can always translate it to "Yes" or "No"."

I need it to translate to "Yes"" or "No" for a 3rd party application, not for a report or anything within SAGE.

And what event DOES fire when you invoice a sales order?

I would ask StarShip if they can do this for you.  Probably very easy for them to do.

It is possible that Sage writes this value without triggering any BOI events.  That would be unusual, but technically possible.  Header Pre-Write would probably be the best bet (with a GetValue added to the script...).  If that does not work, scripting won't be possible for this.

Does the 3rd party application need to connect with the raw data?  A SQL Express View (linked server...) or Access passthough query might work to translate the value into a Y/N without a Sage script.

In my test, the script did not fire on either event attached to SO_SalesOrderHeader but I would think you could instead put the script on the post write event of SO_InvoiceHeader and get the value of the sales order number and then get a handle to the SO_SalesOrder_Bus object and set the value in the UDF yourself.

Kevin mentioned some good ideas too regarding using either a SQL view or Access passthrough query to translate the value if the 3rd party program is using ODBC to read the information from sage 100.

OK you kind of lost me there. I wouldn't know how to do that (first paragraph).

As far as using SQL View or Access passthrough to translate, I don't know that that would work because we still do also need the actual CurrentInvoiceNo to come through.

OK you kind of lost me there. I wouldn't know how to do that (first paragraph).

As far as using SQL View or Access passthrough to translate, I don't know that that would work because we still do also need the actual CurrentInvoiceNo to come through.

What it the 3rd party external software trying to do?  Why does it need the value?  The SQL / Access idea would just provide reshaped query results, not change the way Sage works.

It's Starship. We are trying to filter out the orders that have already been invoiced. It pulls from SO Header. The only field in SO Header that changes when a sales order is invoiced is CurrentInvoiceNo (as far as I can tell).

I can add CurrentInvoiceNo to the filter fields, but the only operators available are Equals, Not Equal, Contains, and Begins With. I can't do CurrentInvoiceNo LESS THAN 1 like I would in an ALE lookup.

I have no way of using the filters to only show the orders with a blank CurrentInvoiceNo field. 

If I had an Invoice Created UDF and it had a value of Y if CurrentInvoiceNo had a value, then I could do 'Not Equal to Y'

Not familiar enough with StarShip.

Can you configure StarShip to use a custom SQL statement instead of a table?

Can you use the Equals operator with a blank value?

Can you use the Equals operator with a value set to NUL, NULL, nul, or null?

At the very least, you could create an Access Passthrough query with the WHERE clause in the query and point StarShip to it. The WHERE clause would be the following.

WHERE CurrentInvoiceNo IS NULL

No on the custom SQL statement.

I tried equals blank and i tried null. There is no wildcard character either.

I've never heard of Access Passthrough but I can look into it. 

Have you ever used Business Insights Explorer?

You can create a new report by walking though the wizard and when you get to the filter screen, you can use Less than 0 on the CurrentInvoiceNo field, this will create a SQL view with whatever name you gave the report (no spaces or underscores btw).

"you could instead put the script on the post write event of SO_InvoiceHeader and get the value of the sales order number and then get a handle to the SO_SalesOrder_Bus object and set the value in the UDF yourself."

How would this script look?