Working with Lookup columns in SharePoint list forms with InfoPath

Hi everyone!

I would like to show you how to work with lookup columns between lists when you i.eg. would like to summarize the amount of orders that a sales person have made.

So we have a list called “Sales persons” with five columns, “Name”, “Region”, “Office”, “Sales ID” and “TotalAmount”.

We also have a list called “Sales orders” with three columns, “Product”, “Amount” and “Sales ID”.

In this list the column “Sales ID” is a lookup column to “Sales ID” in “Sales person”.

We also have a data connection to retrieve these three fields from “Sales orders”.

When we open a form for a sales person we want to see the total amount of orders that’s been made by that person. When we first look at this it looks quite simple. The “TotalAmount” should be the sum of “Amount” where “Sales ID” equals “Sales ID”, right?

The problem here is when we work with lookup columns it is not actually the value of the look up that is brought to your repeating table by your data connection, it is in fact the “ID” of the item in that list.

So to filter which amounts to summarize we should do like this:

The “TotalAmount” should be the sum of “Amount” where “ID” equals “Sales ID”.

Now when we open the form of a sales person, we could see the total amount that person has sold for.

Hope this helps someone!

CU

/SPNiax

Advertisements

2 Comments on “Working with Lookup columns in SharePoint list forms with InfoPath”

  1. sr says:

    This seems to not work for web-enabled forms (is is possible on a web enabled form?)

    • SPNiax says:

      Hi kind of late answer, sorry for that.
      In the blog post I have used InfoPath to edit the lists form, through the IP-button in the ribbon.
      This means that it is a web form that is being used, because list forms edited with IP are run by Forms services.

      So a short answer would be, yes it is.

      /SPNiax


Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s