David, this isn't possible, though I am not sure if I understand the issue.

You're not able set the Despatch Receipt Number (this is generated by Sage).

Thanks

Thank you for advising me.

I have two possible solutions to this issue and would like to know which one the Iman Sage 200 2016 connector is more likely to support.

Solution 1 : Add ticket number as an extra field on the SOPDespatchReceiptLine table via Sage Object Store Builder. This would result in a SQL Table called SOPDespatchReceiptLineX containing the despatch receipt line id and the ticket number.

Solution 2 : Add a custom table via Sage Object Store Builder that contains an id, the DespatchReceiptNo and Ticket Number.

David, in reply to Solution 2 what is the id field? Is it just the id to the record in the custom table (and not the ReceiptDespatchLineId)?

Thanks

In solution 2,  the id is just the id to the record. 

David, solution 2 would be the better approach.

However, you'd need to insert the records directly using the DB Writer (as the Sage200 Connector doesn't have the ability to write to custom tables).

Thanks

Thank you for the advice. I have adopted solution 2. I need to get the max id of the custom table to set the unique record ids so I set up a lookup with the select clause Max(LineTicketId) as MaxLineId.

When I try to use the lookup, I keep getting type mismatch on Resolved Function - LOOKUP("GETMAXLINEID","MaxLineId",,False) .  What do I need to do to get this to work? The custom table is currently empty.

David, you're missing a value in the third argument. Alter the lookup to include an empty string.

Lookup("GETMAXLINEID","MaxLineId","",False)

Thanks

I have set the value in the third argument. It's possible that MaxLineId doesn't contain a value because there are no records on file. I have tried various formulas but just keep getting errors like the one below:

David, its because your Lookup is returning.

I would suggest you alter the Lookup query to something like..

ISNULL(Max(LineTicketId), 0) as MaxLineId

Then your script is simple

Lookup("GETMAXLINEID","MaxLineId","",False)

Thank you for help with this. Everything works now.