InfoPath Dev

Imagine i have a purchase order form which is linked to a secondary data source supplying the goods that can be purchased and price per item.

Primary Data Source
Repeating
@ItemName, @ItemPrice, @ItemQty, @LineCost

Secondary Data Source
@ProductName, @ProductPrice

the customer fills out the order by selecting an item form the drop-down list @ItemName with the values from @ProductName and inputting the quantity required in text box @ItemQty. Using rules i set the @ItemPrice value relative to the name of the product from the secondary data source ie where @ItemName = @ProductName. Then i autocalculate the @LineCost by multiplying @ItemPrice by @ItemQty.

PROBLEM
The rule to set the @ItemPrice field works fine for the first line of the repeating table but falls down on subsequent lines if the @ProductPrice value is larger than the line above, in which case it defaults to the first line's value for @ItemPrice. If the @ProductPrice is lower it will work fine, but only as
long as on each subsequent line the @ItemPrice is lower or equal to the lines above.

Furthermore, if items had a value of only $1 or $2 then you could select an item priced at $2 in the first line and $1 in the second line but subsequently you would see a @ItemPrice of $1 no matter what you selected.

WORKAROUND
Altering the set a fields value rule to
@ItemPrice where @ProductName = current()/@ItemName
has no impact in helping solve this rule but after a year of trying i have worked a way around it. GET THE USER TO SELECT THE PRICE FILTERED BY THE SAME CRITERIA. Change the @ItemPrice field to a drop-down list box and validate it not to be blank. Supply the price list from the secondary data source list @ProductPrice but filter it where @ItemName = @ProductName which means there is only one item in the list, THE CORRECT PRICE.

ANALYSIS
I cannot help but feel that the automatic rule only populating values lower than the first harks back to filtering in Excel and ordering. This presents a massive problem to proper development of infopath as a forms tool where it cannot through its own rules satisfy this not very demanding criteria. I hope that a fix for this is in the pipeline for Infopath 2007.

This is an ineligant solution and would be FAR better if the filter rule applied to the 'set a fields value' in the clean way it filters the secondary data source in the drop-down list.

What am i doing wrong or is this a problem with Infopath? Somebody tell me as i am less than happy to use this workaround. I have also had other Infopath developers ask me of a way around this and up until now i have not been able to get it to work at all.

Cheers Adam

do you have a unique id field in your product list?

If so add a single hidden field, when a product is selected populate the hidden field with the id, then use the hidden field to filter info to populate the rows, that way you avoid the confusion to which row you are refering to.

I explained that rather badly. I did find this technique on this web site so look around for a better description

I am having the same problem. I am using a unique ID as Stavi suggested, and all of my results are constrained by previous results in the repeating table. For example, my lookup up is:

UID FName LName
CT-1 Bob Smith
CT-2 Jason Green
CT-3 Mark Johnson

In my form I store have a picklist that displays LName and stores UID in a field contactID

I have set up a rule that set's values for FName and LName with filters WHERE contactID=UID.

When I use the form the first instance populates correctly. The scond one will only populate with data WHERE contactID is equal to or less than that of the previous instance. For example:

• I select CT-2 (Jason Green) in the 1st row and it populates correctly with Jasonn Green


• I select CT-1 (Bob Smith)in the 2nd row and it populates correctly with Bob Smith


• I select CT-3 (Mark Johnson) for the 3rd row and it populates incorrectly with Bob Smith

I've tried using the solution provided on this site (Automatically Populate Fields from a Drop-Down Selection) and it still does not work. This method uses the current() function as oppsed to the built in filter.

Can any one tell me exactly what the filter feature is doing or how to correct this?


Thank you,

Ren

Instead of using rules you should try setting the default value of the Price field to the lookup value filtered on the selected product. That should solve your issue.

Shiraz

 Thanks for your effort but even doing it on default values it exhibits the descending nature.  I am working now on populating employee number from full name, works a treat on filtering the default value until the employee number is greater than the max of the previous ones, then employee number equals the previous max.

 This is getting really frustrating now and i could do with some workaround

Cheers Adam