Custom Fields on Sales Order

Discussions relating to breakout scripting, .NET and COM programming with Jiwa objects.

Custom Fields on Sales Order

Postby SBarnes » Wed Nov 19, 2014 10:36 am

Hi Mike

Is there any reason why the contents field is SO_CustomSettingValues is only 255 varchar and can it be made any bigger, my reason is we may need to store another file format (EDIFACT) against an order to pass to anther system, these orders would come in through the XML import?
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Custom Fields on Sales Order

Postby Mike.Sheen » Wed Nov 19, 2014 10:48 am

SBarnes wrote:Is there any reason why the contents field is SO_CustomSettingValues is only 255 varchar and can it be made any bigger, my reason is we may need to store another file format (EDIFACT) against an order to pass to anther system, these orders would come in through the XML import?


The reason for the limit was consistency with other varchar fields in the system storing long descriptions, et cetera.

SQL Server has row size limits - 8060 I think is what SQL 2014 has a limit of - but you can (as of SQL 2005 I think) use VARCHAR(MAX) to utilise row overflow storage automatically. If you want to store documents, in a custom field you should probably instead insert an entry into SY_File - that has an image column which can store up to 2GB files, and then set the SO_CustomSettingValues value to be the SY_File.FileID.

You could *try* altering the SO_CustomSettingValues.Contents column to be VARCHAR(MAX) and see if the software doesn't truncate on insert or update - I can't check right now, unfortunately (a hardware fault took some of our machines offline, including my Version 6 sourcecode VM - I'm still working on getting that back online). I'll check it when I can, but it would be easy to test it out yourself -

Code: Select all
ALTER TABLE SO_CustomSettingValues ALTER COLUMN Contents VARCHAR(MAX) NULL


And to put it back:

Code: Select all
ALTER TABLE SO_CustomSettingValues ALTER COLUMN Contents VARCHAR(255) NULL


Of course - as always - don't do this testing on a production database!

Mike
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Custom Fields on Sales Order

Postby SBarnes » Wed Nov 19, 2014 11:25 am

Hi Mike,

Short answer is don't try it in any database essentially Jiwa gives an error about truncating binary to text and SQL server's not happy about the index on the field for anything over 900 in length.

Thanks for the help.
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Custom Fields on Sales Order

Postby Mike.Sheen » Thu Nov 20, 2014 6:51 pm

SBarnes wrote:Short answer is don't try it in any database essentially Jiwa gives an error about truncating binary to text and SQL server's not happy about the index on the field for anything over 900 in length.


Ahh - thanks for testing that out yourself - at least we know the answer now - my apologies for not being able to give you the answer at the time.

Do need some guidance on how to use the SY_File technique to achieve what you want?

Mike
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Custom Fields on Sales Order

Postby SBarnes » Thu Nov 27, 2014 3:32 pm

Hi Mike,

that would be great.

Thanks
Regards
Stuart Barnes
SBarnes
Shihan
Shihan
 
Posts: 1617
Joined: Fri Aug 15, 2008 3:27 pm
Topics Solved: 175

Re: Custom Fields on Sales Order

Postby Mike.Sheen » Thu Nov 27, 2014 10:15 pm

SBarnes wrote:Hi Mike,

that would be great.

Thanks


Hi Stuart - I'm afraid you will have to afford me a day or two to provide that - I'm pretty snowed under at the moment - but as soon as I can I'll provide you with a sample - do you want it in .NET or in VB6, or in vbscript?

Mike
Mike Sheen
Chief Software Engineer
Jiwa Financials

If I do answer your question to your satisfaction, please mark it as the post solving the topic so others with the same issue can readily identify the solution
User avatar
Mike.Sheen
Overflow Error
Overflow Error
 
Posts: 2440
Joined: Tue Feb 12, 2008 11:12 am
Location: Perth, Republic of Western Australia
Topics Solved: 755

Re: Custom Fields on Sales Order

Postby Ernst » Tue Jan 07, 2020 8:24 am

The Contents(MAX) seems to work well in IN_Custom
User avatar
Ernst
Kohai
Kohai
 
Posts: 216
Joined: Tue Feb 19, 2008 3:43 pm
Topics Solved: 12


Return to Technical / Programming

Who is online

Users browsing this forum: No registered users and 1 guest