InfoPath Dev

I am fairly new at Infopath and hope that I am about to explain effectively what I need. I have a SQL database that is used as the source for both input and data retrieval. The problem is that the Main Connection for my Infopath 2003 form must be the data input connection (so other forums have indicated) otherwise they will not work as expected to be able to update the data if at all. Alternatively, it would seem my data retrieval connection also needs to be the Main Connection to get the cascading dropdowns to work properly (so I have found out using trial and error). Here's the scenario.

DATA CONNECTION (1)
For one form, I need to pull data from the database that will populate two dropdowns: one is LOCATION, the other is COMPANY. These two tables in the database are joined by a third table "LOCCOY" that takes care of the many-to-many relationship that LOCATION and COMPANY have with each other. I use a filter on each of the dropdowns to filter out duplicate values.

• Location dropdown: not(.=../preceding-sibling::*/@LOCATION)
• Company dropdown: not(.=../preceding-sibling::*/@COMPANY)

This works great if the LOCATION, COMPANY and LOCCOY tables are setup in the Main Connection of the form but set as the secondary, the filter on the second dropdown filters out more than expected.

DATA CONNECTION (2)
The second form allows for updating to the database and although this works, I don't want the person doing the updating to be able to update the LOCATION, COMPANY, DEPARTMENT information. If the user types the LOCATION and COMPANY exactly correct, it will retrieve the DEPARTMENTS associated as well as the related personnel data and allow them to update it but what I would like to do is present the LOCATION and COMPANY to the user via one of two methods.

METHOD 1: Originally, I had thought to use dropdowns in the same form to filter the LOCATION and COMPANY but as I mentioned earlier, this method would require the dropdowns' data connection to be set as the primary connection which doesn't seem to work properly for an update form.

METHOD 2:
Secondly, I figured I would trigger a form load (much like a pop-up) from a button on the primary form, to load a second form that would provide a means to select the LOCATION and COMPANY info (thus allowing the data retrieval as the Main Connection) using the dropdowns that are populated from the database, then have them populate back onto the main forms' LOCATION and COMPANY fields.

The part that I am stuck on is: How do I get the values of form 2 to populate back into the equivalent fields in form 1. I am not particularly good at XML and would appreciate any help you could provide.

Thanks much.

-Shanty

P.S. I've tried 3 times to get the formatting to hold as it is horrid to read otherwise, but I apologize if this isn't easy to read/follow.

Hi Shanty,

First of all, your solutions are very convoluted. Why not just use a Web Service on your database. That will make it all very simple. Install our free database accelerator web service and you'll have this problem solved in an hour or two with no code.

Second of all, if you want to do it all yourself, you can use Input Parameters to drive one form from another. InfoPath 2007 includes new support for this. InfoPath 2003 also supports but it is more complex. To pass input parameters from form A to form B you will need to write code or get the latest version of our free qRules tool which supports grabbing input parameters via rules. Another option is to use Jim Cantwell's XSL import parameters option.

Bottom line: your solution should be simpler. Don't bother with input parameters. Just get our Web service and be done. Microsoft doesn't fill all the potholes in the road from InfoPath to SQL. That's what we are for. We offer the lowest price, highest value web service for InfoPath available. Install the trial for free: http://www.qdabra.com/en/products/DBXL.aspx

While DBXL is cheapest, but if you insist, we could help you with the input parameters approach. It will cost you more in the long run, but we're happy to help if you don't want to take a dependency. Just send me e-mail.

Cheers!

Thanks for replying Patrick and I agree, it is the long way around to getting to a workable solution unfortunately, the company I work for imposes many restrictions and getting through all the red-tape is painful so I work with what I've got.  They are still using Office 2003 with no plans to upgrade for about another year and they do not permit many things including the use of Sharepoint services....yet.  Also, I would have build all my forms as web forms however, they are not using Kerberos authentication on  the servers making it difficult (to say the least).  I have several forms that are working quite well for the 1000s of users we have (big company) and it is only recently that I have had the need to upload to SQL from one of these forms.

I have viewed some of qDabra's software and would like to use it but it would probably be months before they would even approve the purchase so if you would be so kind as to assist with making my workaround work for this so I can get the form into use, I would greatly appreciate it.

 Thanks again.

-Shanty

WOW! A lot of information came out of our discussion, Patrick. I would like to recap it here and I hope I have not skewed it but if I have, please correct me.

 Based on what I explained about my forms, you understood that

• I have a requirement to have a Main Data Connection for (A) submitting data to a SQL DB
• and also have a requirement for a Main Data Connection (B) pulling info for cascading dropdowns from the same SQL DB.  Setting up separate data connections for the 3 dropdowns will slow the form down too much if there is a data connection for each of the three dropdowns.
• the data used in the pull connection is also utilized in the submit / push connection.
• i do not want users submitting the data to be able to change the values of the three tables associated with the dropdowns; only the subordinate 'contact' tables are to be able to be updated.
• I am looking to use 2 forms with the primary one being the submit form using connection A and the secondary being the lookup form using connection B.

 From this information, you suggested 3 viable options:

1. using "Input Parameters" - From the secondary form, the primary form would be launched with the data of the dropdowns from the secondary form selection.  This would involve a considerable amount of code and XML and would also require a full trust of the forms on the server.
2. using "Import Parameters" - Similar to a merge-forms approach, this method uses XSLT and although relatively limited coding required, XSLT is not highly documented but it would appear that one of this sites participants has information in his blog about this.  As recommended by you, I have taken a look at Jim Cantwell's blog on "Pass Parameters to InfoPath".  Having briefly read through this, it appears this could very well be the solution to my problem but I will also review the samples provided in the Infopath SDK as you suggested.
3. using "Pointers" - this method would have the Primary form open the secondary form and after selecting from the dropdowns, it could feed back to the fields in the primary form.  This method would require considerable code.

In addition to the multitude of information you provided, it appears there is some additional reading that I should also review including Greg Collins post of the Microsoft Office Infopath 2003 Developer's Reference.

Thank you for going the extra mile to assist me and I'm glad I could provide you with an interesting issue.

-Shanty

Hi Shanty,

Hope all is going well with you and your form. Here's one more option...

    4.  using "Input Parameters" support in qRules 1.7, create a link in form A using concat that redirects to form B but with input parameters. Form B opens and uses qRules to set the fields based on the input parameters. http://www.infopathdev.com/forums/p/12667/44758.aspx#44758.  

Hello Patrick I just downloaded qRules 1.8, have installed and injected into one of my forms, but seem to be having some issue figuring out the use of the features. I would like to currently leverage the DateDiff function however keep running into function errors even trying to replicate the example exactly how you have in the software instructions. Can you direct me me to any help for qRules, possibly more examples. Thank you in advance, any help is greatly appreciated. I also see you guys offer courses. A colleague and I are actually looking for a solid forms development course.

Hi:

Can you share the value you are setting the Command field to that is causing you issues?

Hello Hilary, yes so I have tried various formats based on the instructions provided. I have a field actually labeled datediff1, I want this field to show the difference between fields 'date1' and 'date2'.

I have various other timestamps in the form however once the sequence is met (meaning the second timestamp button) to populate the 'date2' field, I would like to have 'datediff1' show the difference. So here is the command based on the instructions I used:

DateDiff /date1 /date2 /component=totaldays

I added a rule to the 'datediff1' field setting the Command value = to the above function. I also tried the default example Qdabra lays out in their instructions by formatting this way: DateDiff /date1=2000-01-01T13:00:01 /date2=2000-01-01T13:00:00 /component=totaldays

This also did not work.

Hi:

To use form fields for your /date1 and /date2 parameters, you'll need to use the concat function. So, for the value in the rule action (where you are setting the command), the formula would look like: concat("DateDiff /date1=", Date1, " /date2=", Date2, " /component=totaldays)

qRules installs with a sample form -- you should be able to find it in the folder you installed to -- generally: C:\Program Files\Qdabra Software\Qdabra Rules Library. The sample is called qRulesForm. Right click to open in design mode, and preview it. You can try the different commands and then, in design mode, look at the syntax used on the rules on the different buttons.

Hello Hilary and thank you so much for your prompt response. You response was very helpful. I was able to open the Rules Form and reverse engineer until I got it right. Thank you once again

Glad that helped! Enjoy qRules.... :)