Format rule with currency - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Format rule with currency

Last post 02-14-2017 07:40 AM by Hilary Stoupa. 9 replies.
Page 1 of 1 (10 items)
Sort Posts: Previous Next
  • 02-09-2017 07:48 AM

    Format rule with currency

    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.
    Filed under:
  • 02-09-2017 08:01 AM In reply to

    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)
    Hilary Stoupa

  • 02-13-2017 05:16 AM In reply to

    Re: Format rule with currency

    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.
  • 02-13-2017 08:47 AM In reply to

    Re: Format rule with currency

    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)
    Hilary Stoupa

  • 02-13-2017 09:45 AM In reply to

    Re: Format rule with currency

    NICE! That worked. Thank you so much. Will need to remember this. Now I can put this together how I want.
  • 02-13-2017 11:27 AM In reply to

    Re: Format rule with currency

    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?
  • 02-13-2017 11:29 AM In reply to

    Re: Format rule with currency

    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 Stoupa

  • 02-13-2017 12:25 PM In reply to

    Re: Format rule with currency

    Hilary thank you it does work querying right off of the fulltotal. I added a button and it does retrieve the correct value.
  • 02-14-2017 05:12 AM In reply to

    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.
  • 02-14-2017 07:40 AM In reply to

    Re: Format rule with currency

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

Page 1 of 1 (10 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.