Sage 100

ProvideX Data Question: Is it possible to make a UDF a Key field? I am encountering an error #14 reading AR InvoiceHistoryDetail (with 6M records) via a pass-through query. I can avoid the error if I can filter the pass-through query on a key field, but I need to be able to filter based on a UDF. Ideas? If so, is this advisable?

I believe a UDT has a key field.

Thanks @DougHiggs I need to make a UDF in AR InvoiceHistoryDetail a keyed field though.

There is a providex KEYED directive used to create tables and define the keys to the table. This directive could be used to define a new table with an whatever keys you need. I am not sure what implications that would have on the system. You should consult with a Sage development partner. http://manual.pvxplus.com/

I know you need the new key in ARInvoiceHistoryDetail. You MAY be able to create a new ARInvoiceHistoryDetail with an additional key and transfer the data to the custom table. Consult a Sage developer.

Create a new table using a select query to pull data from the PTQ table?? Could you then make the UDF field a key field in the select query table?

Agree with Jeff, I'd create an update query off the PTQ to a new table, then multi-key the new table. Add an index to the UDF as well.

Can you paste a copy of your pass thru query?

Dan, did you get this resolved? I'm running into the same problem.

I resolved this by filtering by filtering on one of the existing key fields. I needed to reduce the number of records based on a keyed field and was able to do it (with assumptions particular to the customer) on HeaderSeqNo. So, no I didn't find a solution to the original idea. If you do please post.

Well Dan, I found out that the link to AR Inv History Detail for the company was ""damaged"". Once I deleted it and made a new link, the query worked. (I made a temp table on only invoices for the last two years and then link to the detail file.) However, I had to do the same thing for another company (same client) and cannot resolve error #14. There are 7 million records (from 2006!) My first thought is to make a backup company and purge old history. Interestingly enough, I can write crystal report on the same thing without error. (But for various good reasons, I cannot write the custom report in crystal.)