Missed Fields in SQL Server

Comments

• Options

  bbrown Member Posts: 3,268

  2007-06-20

  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.

  There are no bugs - only undocumented features.

  0
• Options

  diptish.naskar Member Posts: 360

  2007-06-21

  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.

  Diptish Naskar

  ---

  For any queries you can also visit my blog site: http://msnavarena.blogspot.com/

  0
• Options

  senthil Member Posts: 9

  2007-06-21

  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

  Regards,
  
  Senthil

  0
• Options

  colin_leversuch-roberts Member Posts: 113

  2007-06-22

  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

  0
• Options

  DenSter Member Posts: 8,304

  2007-06-22

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

  Daniel Rimmelzwaan
  RIS Plus, LLC
  MVP - Business Apps

  0