InfoPath Dev

I have reviewed the Page Master Detail that Greg has developed. I have a couple of questions. Does this work with SQL as the data source? Also if a query is run to filter results will the button page through the records correctly?

Thanks.

Hi Ann and welcome,
Should work with SQL fine and I believe Matt Faus just posted a related HowTo on master detail and query recently athttp://www.infopathdev.com/howto/tutorials/default.aspx?i=0bb37b4de44348408680a53a449f2353. Good luck!

Patrick Halstead [InfoPath MVP]
InfoPathDev

Patrick,

Yes I was excited to see Matt's article. However this does not appear to emulate the next record functionality. This takes the user to another screen to return the results. The user then has to close that form to do more searching.

Perhaps I am missing something, but what my user would like to be able to do is page through the records on one screen, not open another form. That is why the Next Record was appealing.

If Matt has a solution that will emulate that please let me know.

Thanks.

Ann

You can use code behind a button to achieve what you are looking for. You will have to use a specific SQL statement and some rather advanced code to cut the SQL data into "pages". To use SQL statements to query the database check out this code snippet:

XDocument.QueryAdapter.Command = "select * from myTable where id > 12 limit 10";
XDocument.Query();

You will want to look at the LIMIT sql keyword, this will cut your data into sections. If you need any more help, please tell us a little bit more about your form's data structure. I'm glad the article gave you some help at least! Let us know how things turn out!

Patrick: Your link includes the "." and is broken. (We really should fix that, it's pretty annoying)

---------------
Matt Faus
InfoPath Dev Texas

Matt:

Thanks for your response.

I have a form hooked to a single SQL table cataloging software issues, what they are, fixes, status of the issue, etc. The key is a Software Issue No set as an identity field. I would like to emulate the Next Record functionality that Greg Collins demonstrated in the Paged-Detail demo which shows one record at a time on the screen (Greg's example can show multiple per page). The user would click through <<, <, >, >> buttons to first record, records in the middle or last record. I have set that up but have been unable to get the filters and rules that Greg had on the master table to work. I can make this work without Greg's rule if the query on the dataset is wide open. But once I filter on say status, I need further programming to filter the master table to only show the filtered records.

THen I saw your posting. This worked as outlined but creates the extra step of going to another screen as opposed to paging through the information on the same screen. The ideal for me would be replacing the View Details button with the ability to page through any dataset returned through the query function right below the buttons.

I have looked at your statement. I am unclear how that would work in the scenario described above. Would the limit function be on the query button of the dataset? How would the filter then work on the master table?

If everything is working except you are switching to a different page when you don't want to, you can just take that line out of the code (or rule). After that simply copy+paste everything on the second page and put it on the page that you want everything to stay on. Am I understanding you correctly?

The LIMIT function would go in the code behind the query button. However, if you have things working how you need them to, then don't worry about using the LIMIT function with a custom SQL statement.

---------------
Matt Faus
InfoPath Dev Texas

Matt,

I have followed your suggestion and this is close to what the user is looking for.

The problem with the InfoPath master detail scenario is that the user wants to page through the detail on the screen without having to see all of the rows on the master table. The final solution that I am looking for is to page through the top table row by row (only one row at a time) while showing the details below. This gives more screen space for the user to see all of the information.

The area where you currently have the View Details button would have two or four buttons. The first button would show the first record details in the queried data set. The second button would move backwards one record at a time while showing details through the queried data set. The third button would move forward one record through the queried data set. The fourth button would go to the last record in the queried dataset. Your solution almost accomplishes this except that all of the rows of the master table are displayed all the time.

It occurs to me that to accomplish this I would need to track the next record ID NO or Previous ID No of the queried dataset much like you are doing for the current ID through the DetailID. I have been unable to resolve a way to do this. Any suggestions would be appreciated.

Again thanks for your reply.

Ann

If you have a "master" table that only displays one row why even have it? When you think of a master/detail relationship it is generally assumed that the master table shows several rows with only a little bit of information and the detail table shows one row (or a few) with a lot of information, as in the example.

Yes, you will need to keep track of the Previous, Current, and Next IDs for your database. To do this, you will have three nodes to store the values in. Starting at ID=1, the previous button will be disabled and the next button will requery at ID=2. Similiarly, when you get to the end of the records the next button will be disabled.

Would you mind posting the code behind all four of your buttons? I am curious to see this and am sure that it would help a lot of people. Thanks a lot and I hope you can figure things out!

---------------
Matt Faus
InfoPath Dev Texas

Matt,

Any idea on how to retrieve the next record ID and previous record ID? I have tried using preceding-sibling and following-sibling with no success.


Thanks.

Ann

You might be able to do this using XPath, but I think using SQL statements would be easier.

Using the SQL information and code I listed above your psuedo-code for onLoad will look like this:

*Query the database to get the first 2 records (use ORDER BY 'id' DESC LIMIT 2)
*set currentID to the first ID returned
*set nextID to the second ID returned
*disable the previous button

OnClick for NextButton:
*query database to get the detail for the next record and the ID for the record after that (use ORDER BY 'id' DESC LIMIT nextID, 2)
*set previousID to currentID
*set currentID to nextID
*set nextID to second record returned from the database

I hope this helps - it will require some knowledge of SQL statements, but I think you should be able to get it to work!

---------------
Matt Faus
InfoPath Dev Texas

Use a dropdown and a rule to select the detail

Patrick Halstead [InfoPath MVP]
InfoPathDev

Matt and Patrick:

Here is link that put this all in place for me.

http://enterprise-solutions.swits.net/infopath/sequential-db-table-record-navigation.htm

Thanks again for your help.

Ann

Hi Ann,
Thanks for posting the HowTo link. Sorry we didn't find it for you! Or, was this something you created?

Patrick Halstead [InfoPath MVP]
InfoPathDev