0

I have an infopath form on my sharepoint server that will be used as an order form. Keep in mind it is a 2007 template.

I have a list of products in a table with text fields where customers can enter in the quantity. I currently have the prices stored in an Excel sheet. I was wondering if there is any way to have infopath pull these prices from excel and do calculations to find the grand total of all quantities of products.

The only way I can see to do it right now is to make a hidden text field which calculates the subtotal, and then the grand total adds these up. However I have hundreds of products and this is very time consuming.

Improve this question

1 Answer 1

Reset to default
1

An option would be to store all the Excel data in a SharePoint list. Then you can create a datasource to consume that list data and pull back the item name and it's price.

Then in the InfoPath form, I would create a repeating table, bind a drop down control to the datasource to the product name. I would create a rule that when this drop down field is changed it populates a read only field in the same repeating row that shows the price from the selected item. Then I would have a totals row that calculates all of the cost values for a grand total.

Improve this answer
3
  • This sounds like a great idea and it is what I was thinking of doing, but I ran into 2 problems: 1) Is there any ways to have the item name search the data source and match the item to the price? It would take a long time to manaually match them up. 2) is there a way to skip the read only text field that calculates the cost? That is really the step I am trying to avoid as I would have to make a red only field for every product which would take forever. I wanted a way for all calulations to be done at once in the background and then spit out the grand total. Not sure if this is possible.
    – Ben Taborek
    Jun 11, 2015 at 14:25
  • The point would be that when you bind the datasource to the dropdown to select the product, it already knows the price, so it puts it in the text field. I didn't write that quite right above, I'll edit it.
    – Eric Alexander
    Jun 11, 2015 at 18:23
  • Thanks, that works. The only issues is my SharePoint server is 2007 which doesn't support data filtering for an in browser form :( I'm just going to use a form filler though because then it works in InfoPath 2010
    – Ben Taborek
    Jun 11, 2015 at 18:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.