Hi “Navis” ! I try to fill a salesline directly from SQL (for example with ASP ). But theres is no way to leave a field blank. Does anyone have good idea to solve this problem without changing the Databasestrukture ?
quote:
Originally posted by alexx: Hi “Navis” ! I try to fill a salesline directly from SQL (for example with ASP ). But theres is no way to leave a field blank. Does anyone have good idea to solve this problem without changing the Databasestrukture ?
Hi! Which field are you talking about? Bostjan
Hello Bostjan All fields in tables like “sales line” has to have filled completly. It is not permitted to leave several fields blank.
Murat Kepir, Not quite true and then again not. Some fields can be left empty/blank (Description 2, Discount etc.). It all depends if the fields is the basic for other calculation/validation etc. and when you fill a sales line directly from SQL/ASP then you must manually insert all the data that the OnValidate code in Attain normally inserts. To say it in another way: When updating Attain externally then you must recreate all the business logic normally done by Attain internally. Best regards, Erik P. Ernst, webmaster
Alexx - is the problem you’re running into the fact that the Navision tables don’t allow you to have a NULL value? You have two choices - one is to go into Enterprise Manager and change the fields to allow Nulls. Alternatively (and this is what I’ve done before, and would suggest) - I’ve created a “scratch table” that I import all my records into - then I fire a codeunit that copies the records from that scratch table to my navision table - in this case, the Sales line table. This has the benefits of not modifiying the navision database without using its business logic, ,but allowing use of the SQL server set of DTS tools. Daniel Day Developer Elliott Davis Technology Solutions http://www.edtsolutions.com
I think that Daniel’s suggestion is best. Let’s not forget about having to update the FlowFields as well, which I am unsure as to how they will react when adds are done outside of Navision. Daniel’s way would avoid this problem. I personally would avoid chaning the data structure on SQL directly unless you are exactly sure of the consequences.
Thank You for your replies ! But i want to get realtime information about the Sales Line. Therefor i have to use real the Table. Daniels method (“scratch table”) is the easiest way but i have to Run a Codeunit. I prefer to insert records directly. I think there is no problem to insert all important fields, but there are some they dont accept “input” like decimal-Fields and others they dont be left blank. To change the Database-Structure can solve problems, but what kind of problems ?
I would not recommend You to change the structure. Instead You should insert string like ‘’ in text and code fields and 1953-01-01 in date fields where You want blank fields in Navision. It’s a lot of work with SQL-statements and You have to be careful afterwards when changing tables in Navision, then You must change Your SQL-statement also. //Lars
Hi Lars You´re right. I can fill all fields with ‘’ and so on but whats about decimalfields ?
Zeros should work for numeric fields - thats the way navision handles them anyway. Daniel Day Developer Elliott Davis Technology Solutions http://www.edtsolutions.com
You can create a paralel structure (Sales Header2 and Sales Line2), only with necesary fields. In Sql, create a view to show Sales header + Sales header2 and Sales line and Sales line2. From ASP, insert data in paralel structure. In Navision, create a procedure to transfer data from Sales line2 to Sales Lin and delete Sales line2 and from Sales Header2 to Sales header. You only need 10 or 20 fiels in both. This process can be executed in Main Menu (330) on OnOpenForm Trigger. Best regards from BCN.
You could create SQL Server “DEFAULT CONSTRAINTS” for each column, using the ALTER TABLE statement (or use Enterprise Manager), setting default values for each data type. The columns can then be omitted from an INSERT statement. The defaults (or undefined) values for Attain data types are: Integer, Option, Boolean, BigInteger, Duration: 0 Decimal: 0.0 Text, Code, DateFormula: ‘’ (or 0 if Code is an Integer SQL Data Type) Date, Time, DateTime: 01-01-1753 00:00:00.000 BLOB 0x00 or NULL Binary, TableFilter: 0x00 GUID: 00000000-0000-0000-0000-000000000000