Infopath 2010 (part of MS Office Professional Plus 2010) version 14.0.61269.5000 (32 bit)
SQL Server 10.50.1600
Snapshot describing the table structure, InfoPath myFields panel and sample demonstration of issue:
I'm using Infopath (database template) as interface for 2 tables in a SQL database. The first table (let’s call it Config Table) is a dimension table (contains various attributes like metric name, goal etc.). The second table is a fact table (let’s call it Data Table) and stores the actual data periodically. The Data Table has a Foreign Key that binds to the Primary Key of Config Table.
I’ve been successful at creating the InfoPath form such that users can create new metrics in Config Table and later on, populate Data Table with data points on weekly basis (using Repeating Table inside Repeating Table).
The issue I’m facing is that while updating (adding or updating the existing data points) in the Data Table, InfoPath overwrites some of the data points in Data Table (thus creating duplicate rows).
I have tried creating multiple databases on different systems and tried different InfoPath forms (SQL Server 2008 R2, SQL Server 2012, InfoPath 2010, InfoPath 2013) and can replicate the problem repeatedly. Most likely it has something to do with how InfoPath parses the Insert & Update queries to SQL.
Has somebody encountered the same issue before? Is this a known issue with InfoPath? And if it is a bug, can somebody help me report it to Microsoft? Any workaround in the meantime would be appreciated too. Thanks for all the help.