URGENT! Current() function

[Adam Harding]

I am looking to filter my Skill info from my secondary datasource
dfs:/myFields/dfs:datafields/d:Category_Sub_for_Job_Skills/@Skill
by the Category
(dfs:/myFields/dfs:datafields/d:Category_Sub_for_Job_Skills/@Category)
equivalent to the Skills against the Category selected on my
form(dfs:/myFields/dfs:datafields/d:Users_Info/d:Category_Level/@Category)

I have treid using the current() function with my arguement reading:
@Category !=
xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:Users_Info/d:Category_Level/@Category = current()/dfs:myFields/dfs:dataFields/d:Users_Info/@Category

If i leave out the '!' it does the opposite of what i need giving me every
skill NOT equivalent to the category selected on the form. Furthermore, it
stops working after row one. I am going wrong somewhere but not having done
scripting before i am way out of my depth.

Please someone help as it is blowing my mind.

 

[Scott L. Heim [MSFT]]

Hi Adam,

Please test the following steps (these use the Northwind sample SQL Server
database.)

- Create a new, blank InfoPath form
- Add a Repeating table with 2 columns
- Change the name of text box in the first column to: txtCategories
- Change the name of the text box in the second column to: txtProducts
- Add a new "receive" data connection to the Categories table in the
Northwind sample database (you only will need the CategoryID and
CategoryName fields)
- Add a new "receive" data connection to the Products table in the
Northwind sample database (you only will need the ProductID, ProductName
and CategoryID fields)
- Right-click on both txtCategories and txtProducts, choose Change To and
select Drop-Down List
- Right-click on txtCategories and choose Properties
- For the List Box Entries, choose the option "Look up values in a data
connection to a database..."
- From the Data Connection box choose Categories
- Click the Select XPath button next to Entries and choose d:Categories
- Leave Values as "@CategoryID" but click the Select XPath button next to
Display Name and select CategoryName
- Right-click on Products and choose Properties
- For the List Box Entries, choose the option "Look up values in a data
connection to a database..."
- From the Data Connection box choose Products

** Now we begin to filter! **

- Click the Select XPath button next to Entries and choose droducts
- Click the Filter Data button
- Click Add
- In the first drop-down box choose CategoryID
- In the second drop-down box leave the default: is equal to
- In the third drop-down box choose Select a Field or Group
- From the Data source box choose Main
- Expand the groups until you can select txtCategories and then click OK
- Click OK again
- Click Modify
- From the first drop-down box choose The expression - you should then see
the following:

@CategoryID =
xdXDocument:get-DOM()/my:myFields/my:group1/my:group2/my:txtCategories

- Modify this to the following:

@CategoryID = current()/my:txtCategories

- Click OK until you are back to your form

** Now we need to clear out the Products list after each selection

- Right-click on txtCategories and choose Properties
- Click the Rules button
- Click Add
- Click Add Action
- From the Action box choose Set a field's value
- Click the button next to "Field" and choose txtProducts
- Leave the Value field blank!
- Click OK 4 times to get you back to your form
- Preview and test by adding rows to the table - each time you select a
category the products list should be refreshed and only include those
products that match the selected category!

Let me know how this works for you!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Adam Harding]

Done as you have described and products field shows nothing at all even on
the first row. By the way ALL my datasources are in Access does this make a
difference?

 

[Adam Harding]

Sorry Scott MY FAULT i added the products table as a subsidiary of the
categories, changed that and it works fine

 

[Scott L. Heim [MSFT]]

Hi Adam,

That's great to hear! So do those steps help provide an understanding of
what you need to do in your actual solution?

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Adam Harding]

Now works fine on my 2nd of 3 drop down lists but i cannot now get it to work
on the third!!

I am trying @Category_Sub-Sub = current()/@Sub-Sub_Category

But it results in a blank drop-down list any ideas why? Do i have to have
the two fields identically named maybe?

 

[Scott L. Heim [MSFT]]

Hi Adam,

So what behavior are you seeing? Does the 3rd drop-down list contain the
correct items "along with" the previous items or does it not contain the
correct items at all?

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Adam Harding]

Cheers Scott works well on Dropdown 2 but i cannot work it for DD3 it returns
a blank drop-down. The fields are now named the same so my expression is
@Sub-Sub_Category = current()/@Sub-Sub_Category which was amended from a
straight filter where
@Sub-Sub_Category =
xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:Users_Info/d:Category_Level/d:Competency_Level/@Sub-Sub_Category

Any ideas?

 

[Adam Harding]

If i do not use current () the results pile up but using current clears ALL
records from the drop down list

 

[Scott L. Heim [MSFT]]

Hi Adam,

Just to be sure - the filter should basically be:

"field from secondary datasource is equal to the field on my form"

So in your case:

@Sub-Sub_Category = current()/@Sub-Sub_Category

@Sub-Sub_Category = current()/<the field on your form>

Is this what your filter expression references? Also, make sure if your
secondary data source is expecting an "ID" (i.e. like the sample I provided
- CategoryID) that the field on your form is "bound" to a numeric value and
not the "display text."

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Adam Harding]

My filter says exactly that, the field is not a number funtion it is all
text. Annoything is that after getting it to work on Drop-down 2 it doesn't
work on DD3 I must be missing something. Anyway here is my statement

@Sub-Sub_Category = current()/@Sub-Sub_Category

Do i need to insert a /../ maybe or something else to fix this any idaes?

 

[Adam Harding]

If i set my filter to get the current @Sub-Sub_Category equal to
@Sub-Sub_Category the expression box reads:-

@Sub-Sub_Category =
xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/d:Users_Info/d:Category_Level/d:Competency_Level/@Sub-Sub_Category

Does that give you any clues?

 

[Adam Harding]

Sorry mis-type ignoe the work current in the previous post a complete mental
slip on my part.

 

[Scott L. Heim [MSFT]]

Hi Adam,

That expression does not seem correct - I believe it should be something
like:

@Sub-Sub_Category = current()/@Sub-Sub_Category

The sample I previously provided only display 2 columns - let's make sure
you can successfully get 3 working correctly from Northwind:

- Create a new, blank form
- Add a repeating table with 3 columns
- Change the names as follows: txtCustomers, txtOrders, txtProductsOnOrder
- Change each to a drop-down list
- Add 3 data connections as follows:
- Customers
- CustomerID
- CompanyName
- Orders
- OrderID
- CustomerID
- Order Details
- OrderID
- ProductID

- Set the data source for txtCustomers to Customers
- Set the data source for txtOrders to Orders with a filter as follows:
@CustomerID = current()/my:txtCustomers

- Set the data source for txtProdcutsOnOrder to Order Details with a filter
as follows:
@OrderID = current()/my:txtOrders

- Add a Rule to txtCustomers:
- Action: "Set a field's value"
- Field: txtOrders
- Value: Leave this blank

- Add a Rule to txtOrders:
- Action: "Set a field's value"
- Field: txtProductsOnOrder
- Value: Leave this blank

- Test - once you select a customer you should get a limited set of Orders
and once you select an order your should see a filtered set of products on
that order.

Let me know if this works as expected!

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Adam Harding]

Done that works apart from the txtcustomers goes blank. One thing i noticed
on the Order filter was that when i filtered order by customer ID it gave the
start of the expression as

.../@customers = current()/my:txtcustomers

does the ../ at the start signify everything?

Greg also mentions below that any filter i make has to account for the
previous filter as well. could this be it?

Still getting the empty drop down in dd3

 

[Adam Harding]

Used Greg's idea of incorporating the filter for the level above as well and
it works up to a point. If i have Systems/MSA/Authorisation as my first 3 dd
boxes, then if i select Systems/Excel my third box displays the correct
results AS WELL AS the previous results so my filter on dd3 for the @Category
works fine BUT NOT on the @sub-category. Any further ideas?

 

[Adam Harding]

Cheers Greg that has helped on the way but still not fully ironed out.

This may not be your particular issue, but it is very relevant just the same:

Each level in the cascade must account for all previous levels when filtering...

So if you have drop-downs A, B, C, and D...

A (no filtering)
B filters on A
C filters on A and B
D fitlers on A and B and C

If you don't do this then if you have identical items in B, C will pull in all of those...
You are trying to create a tree here... that's what a cascade really is. you have to account for the full path of the tree all the way back to the root.

--
Greg Collins [InfoPath MVP]
Please visit: http://www.InfoPathDev.com



Now works fine on my 2nd of 3 drop down lists but i cannot now get it to work
on the third!!

I am trying @Category_Sub-Sub = current()/@Sub-Sub_Category

But it results in a blank drop-down list any ideas why? Do i have to have
the two fields identically named maybe?

Sorry Scott MY FAULT i added the products table as a subsidiary of the
categories, changed that and it works fine

 

[Adam Harding]

Furthermore, specifying ANY type of current()/@Sub-Category, apart from
current()/@Category, on dd3 gives no results at all in dd3

 

[Scott L. Heim [MSFT]]

Hi Adam,

So when you followed my steps for using Northwind they did not work for you?

Did you follow the steps *exactly* as I had described? If not, what are you
doing differently? If you followed them exactly, the sample should work
correctly.

Scott L. Heim
Microsoft Developer Support

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Adam Harding]

Sample works fine but i cannot translate my understanding of the process
across to my form.

It seems that ANY use of the current() function in conjunction with
@sub-sub-category renders my DD3 empty i have tried
@sub-sub_category = current()/@sub-sub_category,
@sub-sub_category=getDOM()dfs:myfields/dfs:datafields/d:Users_by_info/d:Category_Level/d:Competency_Level[@sub-sub_category
= current()/@sub-sbu_category which i now know is wrong.
.../@sub-sub_category = current()/../sub-sub_category
and all combinations of the above MAN it is frustrating.

So nearly there but no joy. One odd thing as well for one of my sub-sub
categories it pulls back no results despite it being set up in the database
exactly the same way as all the others. All the others work fine as long as
i ignore the current() function for @sub-sub_category.

Any ideas?