InfoPath Dev

I have a sharepoint list used as a 2ndary data connection that has two columns called Invoice # and Invoice Total. I have dragged the table onto the canvas and I am trying to add a formatting rule d:Invoice_x0020_Total != xdXDocument:get-DOM()/my:myFields/my:FullTotal. These numbers match but the table remains blank. If I do the same using Invoice # it works fine. Is currency not actually currency when the rule runs? The two fields match $5721.05 but fails.

I'm pretty sure SharePoint is returning more decimal places than you may think. See the attached screenshot that shows the Invoice Total format settings - try changing that to "none" so you can see the actual value. Maybe do the same w/ Full Total, so you are seeing the actual XML value for both. Then you can figure out what to do from there. I think the number() function may help, actually, so try changing your comparison in your sample form to the expression: number(d:Invoice_x0020_Total) != number(xdXDocument:get-DOM()/my:myFields/my:FullTotal)

Hi Hilary, Thank you for the reply. I figured as much that SharePoint was returning more zeros. I went to that column and formatted it the same currency and 2 decimal places,but when it is retrieved in InfoPath it doesn't follow the column formatting. I tried using the number function as you suggested but no change. Do you have any other ideas? I was thinking maybe using substring(Invoice_x0020_Total, 1, 5) which returns the correct number but not sure how to pull it all together.

Formatting settings simply determine how the data is displayed in SharePoint, not what the value actually is. So - set up your formatting as you had it then in the condition, change the first drop down to "the expression". After you do that, you can use the number function around each of the fields: number(d:Invoice_x0020_Total) != number(xdXDocument:get-DOM()/my:myFields/my:FullTotal)

NICE! That worked. Thank you so much. Will need to remember this. Now I can put this together how I want.

Hilary maybe you can help me a little more? Could I create a query on the invoice total so I don't have to have the data connection set to always retrieve? I would query using the fulltotal on InvoiceTotal to retrieve the matching invoices. Problem is formatting again because SP has more zeros right?

Well, I'm not sure - perhaps the query would work even w/out the exact number of zeros - have you tried it? Put the query & data fields on your form and add a button to execute the query - enter a value and see what happens?

Hilary thank you it does work querying right off of the fulltotal. I added a button and it does retrieve the correct value.

Thanks again Hilary. Everything is working great and the update to the form went into production this morning. All of your recommendations worked. I created a rule on the submit to check for duplicated invoice totals by using a query rule. Then setting a helper field to true if found. If an invoice total is exactly the same as the one they're currently filling out it is exposed in the secondary data connection table that you helped get working with this expression number(d:Invoice_x0020_Total) != number(xdXDocument:get-DOM()/my:myFields/my:Main/my:Invoices/my:FullTotal). If the invoice is still wants to be submitted, I expose an override submit button at the same time.

Wonderful, glad this is all working as anticipated. :)