InfoPath Dev

This is my first question here so my apologies if I am wording it poorly. Any help would be greatly appreciated. I am food distributor chef and I have customers that come into my kitchen to see new ideas. I am creating a form that their salesman would fill out before the visit and submit. The form would allow them to chose what items they would like me to cook for them. I want the form (and database) to be as clean as I can make it, so I want to divide the food choices into menu sections such as Salads, Appetizers, Burgers, etc. I have set up the database with several tables and one of the tables has a field for MenuiItemID that gets populated from dropdown lists in the form. I set up the dropdown list with a filter so under the appetizer section it only show the appetizer choices (it displays the name of the appetizer but will submit the MenuItemID for that appetizer. It is a repeating section so after they chose the first appetizer, if they want a second one they can click Insert Item and another dropdown list appears. This part works fine. My problem is that when a appetizer is selected under the appetizer area, when the salad area is open, that selected appetizer is showing in the dropdown field and if the user selects a salad it then changes the already selected appetizer to that salad. The dropdown is submitting data to the MenuItemID field in the database so I understand why this is happening, I am looking for a solution. Ideally each menu section would allow the user to chose from items in that area only (which I can accomplish by filtering the MenuCategory) without seeing the items they had previously selected. The tables are set ups as follows (only relevant fields shown) Customer Visit Table: CustomerVisitID Customer Number Customer Name Menu Detail Table: MenuDetailID CustomerVisitID MenuItemID Menu Item Table (secondary data source to provide names for the menu items in the drop downs) MenuItemID MenuItemName Thanks

Hi Andrew,

I understand that the drop-downs are being populated by the initial selection. Although I'm not yet familiar with your form, an approach I usually perform is I add a rule which clears the drop-down field (set the drop-down value to blank) on the condition that this action only runs if the drop-down is not blank. Let me know if this helps.

Hi Don Stephan, Here is a screen shot of the form in which I have selected a breakfast item and have not selected the appetizer item yet. The drop down is displaying the MenuItemID of the Breakfast Item. When I set the drop down value to blank when it is not blank it does not store the data properly (the drop downs are just blank)

Another option I thought of (and I am not sure if this can be done) would be to have some type of control that allows the user to select a menu category (Appetizer, Salad, etc) and then select their menu choice from within that category. If that control could be set up in a repeating section. The user would just pick the category for each new item so I would not have to have a separate repeating section and would therefore eliminate the duplicate storage and display of the choices. I of course do not know how to set up that control if it is possible

The current work around I have come up with is to have the drop down box list all the options (about 120) with the menu items name being set up as Category:Item Name so for example Breakfast: Eggs Benedict. This prevents the duplication issue but I really do not like 120 choices in the drop down. Any other suggestions?

Thank you for the additional information. Here is a short video of a cascading drop-down approach we use, which is quite similar to what you want to have on your form. Basically, there are categories to choose from which filters out the choices, and I've tested this approach on a repeating table and it should work. If not, kindly attach a sample of your form so that we could test it further.

Here is the very rough version of the form in its current set up. I like the idea of the cascading drop downs but I am not sure how to set that up with the Access Back End. Since the MenuCatergoryTbl is tied to the MenuItemTbl and not the CustomerVisitTbl, if I add it to the data source it will not allow me to submit data.

This is the more flushed out original form. For this one I set the menu selections up as check boxes and but the question that show/hide each section. The problem with this method is all the menu choices are on the same table as the customer visit and it creates a bad database structure and does not allow for most of the reporting I want to run. I wanted you to be able to see a more complete version of the form since the other one is just in test mode while I try to determine how to work the drop down lists to submit the data correctly.

Thank you for sending a sample. Upon checking, I was able to verify that Breakfast and Appetizer both use the MenuItemID field. This explains why the value from the first drop down is carried over to the next drop down. Also, when you insert an entry for the first drop down, the second drop down will also have an inserted field, so it's sort of a mirror image. I would recommend having another group specifically for the Appetizer section. Something like this sample image below:

A is the Breakfast group and B is the Appetizer group.

 

 Let us know if this helps.

Hi Don, I am working on rolling out the form and the integration with Access seems to be creating some issues. Is there a reasonable way to convert an InfoPath form to be SharePoint based or would I need to start over?