I have 3 lists A, B and C in the same site and i have to make a View joining them and generate few dynamic columns:
Columns of A: Title (Indexed), Util(number)
Columns of B: Title, Title_A (Lookup), Date (Date), Used(number) - no. of util used
Columns of C: Title, Title_A (Lookup)
For a view i can easily join all the rows and see the entries, but the conditions are:
- A join B should bring only latest values in B for A
- If B has no values for A then it should bring join with "Used" value as 0 and "Date" value as "01/01/0001" (Default .NET Date value)
Blank columns per row with background color or image depending upon the following condition
- Util Status: If "Used"(List B) > "Util"(List A) --> "red" else "green"
- C_Entry: If List C has any entry for A --> "green" else "red"
- Last Date: If latest "Date" after Join > 7 days old --> "red" else "green"
- Link: Image Button with links to "Linked.aspx?Parameter=["ID"(List A)]" page
How can i achieve this with best performance?
Note: This view can contain thousands of elements.
Restrictions: I cannot use rest services or CSOM as the services are not accessible to users but can use server side scripting (if required).