Looking up lists via multiple joins.

stunstall — Mon, 17 Feb 2014 16:02:52 GMT

Hi,

I have three custom lists:

Activity
PN
Budget

In PN, there are three fields:

The first field is ID, the second field is PNRef which is the Title field, and the second is Activity which is a lookup into the Activity list

Activity list only has two fields - ID and Title.

Budget has 3 fields - ID, PN (a lookup into the PN list) and Activity which I have made a calculated field to hold the Activity value of the associated PN record.

What I would like is thus:

On the Budget "add new item" InfoPath form, whenever I select a new PN, I would like the Activity calculated field to update with the display name of the Activity [lookup] of the associated PN record.

In SQL it would be:

SELECT Title from Activities

WHERE Activities.ID = PN.ActivityID

AND PN.ID = (Selected PN on form)

The function that I have created, using filters and data connections boils down to this:

Title[ID = Activity and ID = PN]

But it doesn't work. Whenever I change the selected item in the Budget dropdown, I get the completely wrong activity Title displaying.

Can anyone help me here?

Regards,

Scott

Data Connections

Looking up lists via multiple joins.