InfoPath Dev

I have a feeling I am going over old ground here but I have checked numerous posts and they aren't of a great deal of help for what I am trying to do.

 I should say at the outset that the project is a change to an existing sharepoint site hence the design. I probably wouldn't do it this way if I was doing it from scratch.

 I have two drop downs on a form, Category and Subcategory for a document information panel. We have projects that are broken down historically by categorys eg A1:, A2:, A3:, A4:, A5: (These mean something to our design teams and then subcategorys of A11, A12, A13, A14, A21, A22, A23, A24, A25 etc......... These have descriptions after them but for brevity I have left them off.

 What I need to do is choose the category say A1 and have the subcategory dropdown filtered to A11, A12, A13, A14 only.

What I've done so far.

1) If I leave the main data connection, The correct data appears on the form but the filter wont work. In fact I cant even add a filter as I need to select "lookup values from this forms data source" on the data tab on the drop down lists properties. When you try to do that an point it to the correct field it basically says "You have selected a fieldd where the control saves data. Instead, select a field where the control looks up data".

2) If I add a secondary data connection, my filter (namely - Subcategory begins with substring-before(Category,":") works perfectly except that it seems to query the metadata on the items in the list rather than the properties on the main data source. This means not all the possible Subcategorys appear, only the ones that exist already on documents in the document library. To be clear, Assume there are 30 different subcategorys and there are 3 documents in the library at the moment. When I set it up as per 2) I would get only the 3 subcategorys that are on the documents in the library.

I have tried many other work around but I am getting nowhere. Does anyone have any ideas as to how I could accomplish this or can anyone direct me to helpful information. I have scoured the web for a few days now and this is slowly making me suicidal.

 Any help would be greatly appreciated .

Best wishes,

 Gav

What is the source of these categories?  Where do they reside?  If there isn't already a master list of them, then you need to create one.

• Create a custom list for your categories
• Change the Title field to say SubCategory (this is your unique field)
• Add a column named category
• Add a record for all 30 subcategories, and in the category column, put A1-A5 where relevant

Then, consume that list as a data connection and pull both fields.  Set your category dropdown to the 2nd column and show only unique values.  Then, set your subcategory dropdown to the first field and apply a filter where the category dropdown (IP field) equals the category node of the data connection (SP field).  That will then fitler the subcategory dropdown based on the category dropdown and show all available types.

I appreciate your input. Unfortunately due to other reasons I cant set it up this way. This obviously makes perfect sense but the extent of the change in the rest of the project this would be unacceptable to the project manager.

Is there any way to filter based on the information the way it is.

Thanks,

 Gav