Missed Fields in SQL Server

senthil

Hi,
I am working in Navision 4.0, SP1. If i am looking the SQL Server through Navision i can able to see all fields. But i cann't able to see some fields for the same table if i am looking in SQL directly.
For Eg.,
Table : 18, Customer
Fields: Sales (LCY), Balance (LCY)
The above fields are available if i am looking through Navision, But Cann't in SQL Server directly.

I want to create reports in Some Reporting Tool which is used to create free form and grid Reports. In order to that i have to connect with the navision database in SQL Server through ODBC Driver.

bbrown

The flowfield do not exist in the SQL table definitions. You will see a bunch of other table like Companyname$32$1. These are the flowfield data
There is a Navision document that explains how this works.

diptish.naskar

Hi Senthil,

When you restore a NAV database in SQL server, seperate tables are creted for the flowfields and the data for the flowfields of a particular table are stored in this tables. When you openup forms in NAV that contains the flowfields, it fetches the data from these tables.

senthil

Hi ,
How can we know the flow fields, which flow field tables it is storing and relation ship between the flow field table and exact table.

For Eg.,

In which flow fields table Sales (LCY) and Balane (LCY) fields of customer table(18) are stored.

Because i checked the flow field table in SQL Server. But the the field table has fields like f5, f60, f16500, etc., Here how can we kow the name of the fields

colin_leversuch-roberts

the sift table is numbered with the object id for the base table which is stored in the navision table object , if you examine the trigger on the underlying table you'll be able to see the data relationship.

you should not do this on your production system unless you really have no other option.

although the following script is used for something slightly different it will show which SIFT tables are for which base table. Only where there is data in the SIFT table and you must add your company name in the query.
This is a sql 2005 query, it will not run on sql 2000

You should not run this against yor production server, run this from management studio - hope it's ok to post scripts ?

create table #data
( Sift varchar(50),
rowcnt int,
createdate varchar(30),
obj_id int,
nav_id int,
numkey int identity(1,1)
)
insert into #data(Sift,rowcnt,createdate,obj_id,nav_id)
select ''''+so.name+'''',si.rowcnt,''''+convert(varchar(24),so.crdate)+'''',so.id,
substring(so.name,charindex('$',so.name)+1,( charindex('$',so.name,charindex('$',so.name)+1)-charindex('$',so.name)-1))
from dbo.sysobjects so join dbo.sysindexes si on so.id =si.id
where so.name like '_%$%$%'
and so.xtype='U' and si.indid<2
and rowcnt>0
order by substring(so.name,charindex('$',so.name)+1,( charindex('$',so.name,charindex('$',so.name)+1)-charindex('$',so.name)-1)),
si.rowcnt desc
--
--drop table #data
--select * from #data
declare @table table(c1 varchar(40),c2 int, c3 varchar(30),c4 varchar(50),c5 int)
--
set nocount on
--
declare @loop int,@nav_id int,@sift varchar(30),@rowcnt int,@createdate char(24)
declare @base varchar(40),@count int
set @loop=1
while @loop <= ( select max(numkey) from #data)
begin
select @sift = Sift,@rowcnt = rowcnt,@createdate = createdate,@nav_id=nav_id from #data where numkey=@loop;
select @base = ''''+so.name+'''', @count = sp.rows from dbo.sysobjects so join sys.partitions sp on sp.object_id=so.id
where so.name = (
select oo.[company name]+'$'+replace(replace(oo.name,'/','_'),'.','_')
from dbo.object oo
where oo.type=0 and oo.id = @nav_id and oo.[company name]='your company name'
) and sp.index_id<2
--
insert into @table(c1,c2,c3,c4,c5)
select @sift ,@rowcnt ,@createdate ,@base ,@count
--
set @loop=@loop+1
end

select * from @table

DenSter

<standard warning>
Do not modify the trigger code in any way
</standard Warning>