MS SQL Server auto insert records in second table.

zulq

Hi,
I am using Navision 5.0 SP1 with SQL Server, I have another software which requires a specific table structure which is incompatible with Navision. I created a table in Navision and the required software in SQL server to. My problem now is how do I automatically insert the data into the second table when a record is added using Navision.
Below are the structure of the two different tables:
Navision created table in SQL server:

timestamp
id
length
picture
type

Software Created table in SQL Server:

id
length
picture
type

Any ideas how to achieve this or an alternative solution?
Thanks.

David_Singleton

There are many ways to achieve this. But you have not given enough information to make an informed decision.

You need to give some detail of what you are doing, how many records, how often is the link accessed, which direction is the data, is ti modify read, or are inserts required etc etc.

zulq

Let me outline it clearly:
1. When ever a record is entered using form in Navision the record should automatically be inserted/copied to the second table.
2. When ever a record is modified or deleted it should be applied on the second table as well.
3. All data entry and editing will be done using Navision only.
4. Also the length should be automatically generated, it is the length of the image (don't know how to achieve this as well.)

To be more explicit, there will be a form in Navision with the following fields:

id
picture
type

.
The timestamp field is Navision field. The length should be automatically obtain as the image/picture size/length.
Please let me know if you need any other info/clarification.

David_Singleton

zulq wrote:

Let me outline it clearly:
1. When ever a record is entered using form in Navision the record should automatically be inserted/copied to the second table.
2. When ever a record is modified or deleted it should be applied on the second table as well.
3. All data entry and editing will be done using Navision only.
4. Also the length should be automatically generated, it is the length of the image (don't know how to achieve this as well.)

To be more explicit, there will be a form in Navision with the following fields:

id
picture
type

.
The timestamp field is Navision field. The length should be automatically obtain as the image/picture size/length.
Please let me know if you need any other info/clarification.

OK, so if the record will only be entered in NAV, then you don't need to do anying as such. Just create a SQL view for that other application to see the data in the format that you need. Or it mght be possible for the other application to read directly from NAV.

I don't think you need to replicate this as a second table, unless there is more information we need to know.

zulq

David,
It doesn't work like that, I need the software to read the data from the SQL server and print the data using a special printer that only the software can handle. Also the software requires the Image table structure to be specific otherwise it doesn't even work.

David_Singleton

zulq wrote:

David,
It doesn't work like that, I need the software to read the data from the SQL server and print the data using a special printer that only the software can handle. Also the software requires the Image table structure to be specific otherwise it doesn't even work.

Its such a pity we can't get Laurence Oliver* to ask the questions, I am sure then we would get all the answers.

Its just so much easier if you tell everythign up front, instead of leaking information slowly.

So why can't you use a view?

zulq

Can you elaborate on how to do that i.e. create a view. The software as I said has to connect to real image table and I need the table to be populated when data is entered in the table control by Nav. Don't misunderstand me I was thinking the initial structure I gave would be enough.

Thanks.

David_Singleton

well its not just the structure. Interfacing to another application can be done in many ways in NAV. Without knowing exactly what you need, how can we give thebest solution.

You could use a LinkedObject, you could use Triggers to populate another table, you could use ADO, you might use application server, you might use DTS, (integration services), you might use some T-SQL triggers,the list goes on. So we need as much information as possible, otherwise we spend lots of time discussing a solution, only to find that you forgot some important facts that make the solution unusable.

What you need is to explain what you need. Explain what this other software must find. Normally a program interfacing to SQL will only worry abotu the specific fields it needs, and will completely ignore any additional fields.

You need to explain why the TimeStamp is a problem.

How are you interfacing?

zulq

Ok David,
We have BadgeMaker software which is use for printing eCards and we can only use that software to print the cards. However we want all data entry to be handle by Nav and if possible print the cards from Nav by triggering the printer or such. The card design and layout can only be handle by BadgeMaker and printing the cards as well.
Also BadgeMaker will have to connect to the SQL Server and has to select an image table which has to follow the specified structure. When Navision creates table on SQL server DB it automatically adds the timestamp field and if it's removed the table doesn't work. All I want is to be able to handle the data in Nav and possible trigger the printer also to print the cards. Also the specific table needed by BadgeMaker to be automatically populated when data is inserted in the Nav version of the table.
This is exactly what I want.

David_Singleton

zulq wrote:

Ok David,
We have BadgeMaker software which is use for printing eCards and we can only use that software to print the cards. However we want all data entry to be handle by Nav and if possible print the cards from Nav by triggering the printer or such. The card design and layout can only be handle by BadgeMaker and printing the cards as well.
Also BadgeMaker will have to connect to the SQL Server and has to select an image table which has to follow the specified structure. When Navision creates table on SQL server DB it automatically adds the timestamp field and if it's removed the table doesn't work. All I want is to be able to handle the data in Nav and possible trigger the printer also to print the cards. Also the specific table needed by BadgeMaker to be automatically populated when data is inserted in the Nav version of the table.
This is exactly what I want.

You still havent explained how BadgeMaker interfaces to SQL. If it is ODBC it should just ignore the timestamp field. Have you tried it?

If it doesnt work then just create a view and have badge maker link to the view.

zulq

BadgeMaker uses ODBC to connect to the SQL server directly and requires you to select an image table of a predefined structure.

kine

Ok, I will try it from other side: Have you tried to connect the software directly to your table? If yes, was there some specific error? Or you are only trying to fullfill "requirements" without trying if it will work with the Timestamp field?

"Predefined structure" means that there must be this and this field, but do not say anything if it is table or view (view is like table, you do not notice the difference when accesing it though ODBC if you are just selecting data) or if there cannot be another fields.

zulq

I have tried it and it doesn't work at all. It's written in their documentation and I've tried bypassing that but there's no way. The table must meet the requirements for the software to connect.

kine

Have you tried to create View with only these fields and connect the software to this view?

zulq

Thanks a lot kine it worked. Now I need to automatically detect the image size/length. The length field should be automatically inserted and also the other issues in the post are still pending.

kine

add field into the view defined as

datalength(picture)

It means you need to create view defined like this:

select  id, datalength(picture) as length, picture, type from ...

You need to disable compression on the field in NAV (in field properties in NAV) to have correct size of the raw bitmap.


And if the view is working for you, you do not need to do more because the modifications in the base table will be automatically available in the view...