Typescript backed filter on Pivot table for linked Objects

[Palmaker]

Current Situation: We are working with a pivot table built on two linked Object Types: A (providing the first column in the pivot) and B(contributing two columns derived from its properties).

The Challenge: I need to filter the pivot table based on the project_id property from Object Type B, the project_id does not exist in Object Type A. While I’ve already written the necessary function code, I’m encountering some issues that I can’t seem to resolve.

Question for the Community: What could I be doing wrong and how can I add the function to the filter? Any insights or suggestions would be greatly appreciated!

import {ObjectSet,Objects,ObjectTypeA, ObjectTypeB} from "@foundry/ontology-api"


export class PivotTableFunctions {
    @Function()
    public static getFilteredAggregations(projectIds: string[]): { projectName: string; totalValue: number }[] {
        // Fetch all ObjectTypeA (row groupings remain unchanged)
        const aProjects = ObjectTypeA.search().all();

        // Fetch filtered ObjectTypeB entries directly using projectIds
        const filteredBookings = ObjectTypeB.search()
            .filter(booking => booking.Project_id && projectIds.includes(booking.Project_id))
            .all();

        // Aggregate values by Project_id using a Map for efficient lookups
        const aggregations = filteredBookings.reduce((acc, booking) => {
            const projectId = booking.Project_id!;
            const value = booking.Value || 0; // Ensure a default value if Value is null/undefined
            acc.set(projectId, (acc.get(projectId) || 0) + value);
            return acc;
        }, new Map<string, number>());

        // Map ObjectTypeA data with aggregated values
        return aProjects.map(aProjects => ({
            projectName: aProjects.Name,
            totalValue: aggregations.get(aProjects.Project_id) || 0, // Use `0` if no matching bookings
        }));
    }
}

[sboari]

HI there @Palmaker. I think you got the gist of it in your code solution. There are a few issues to sort out and some potential improvements using Foundry’s own functions API instead of native TS. Take a look at this code:

import {ObjectSet,Objects,ObjectTypeA, ObjectTypeB} from "@foundry/ontology-api"
import {FunctionsMap, Double} from "@foundry/functions-api"


export class PivotTableFunctions {
    @Function()
    public static async getFilteredAggregations(projectIds: string[]): Promise<FunctionsMap<ObjectTypeA, Double>> {
        // Fetch all ObjectTypeA (row groupings remain unchanged)
        const aProjects = await Objects.search().objectTypeA().allAsync();

        // Fetch filtered ObjectTypeB entries directly using projectIds and aggregate booking value
        const filteredBookings = await Objects.search().objectTypeB()
            .filter(booking => booking.Project_id.exactMatch(...projectIds))
            .groupBy(booking => booking.Project_id.exactValues({maxBuckets: n}))
            .sum(booking => booking.value);
        
        // Map results
        const mapProjectValues = new Map <string, Double>()
        filteredBookings.buckets.forEach(bucket => {
            mapProjectValues.set(bucket.key.toString(), bucket.value || 0); 

        });

        // Create a FunctionsMap to store the results of ObjectTypeA data with aggregated values
        const resultMap = new FunctionsMap<ObjectTypeA, Double>();

        // Map results
        aProjects.forEach(project => {
            resultMap.set(project, mapProjectValues.get(project.Project_id) || 0)
        }); // Use `0` if no matching bookings
       return resultMap;
    }
}

Main changes:

• The calls to Objects.search() was being done directly on ObjectsA.search(), ObjectsB.search() which, to my understanding, is not how it should be done. Rather, you call on Objects.search().yourObjectAPI() …
• There are foundry-native ways to do the aggregations, so you don’t have to write custom logic from scratch by using the .groupBy clause after you call the search and filter (see here: https://alixpartners.palantirfoundry.com/docs/foundry/functions/api-object-sets#grouping-objects-by-properties and here: https://alixpartners.palantirfoundry.com/docs/foundry/notepad/widgets-functions#writing-batched-functions, for groupBy and FunctionsMap docs, respectively).
• Changed the code to async in case there are a large number of projects for your use case, can be changed back if needed.
• Removed the static declaration in the function definition as it wouldn’t let me test with that on, at least on the instance I’m working on.
• You need to change the n {maxBuckets: n} to your desired number of max projects B tied to projects A (usual FoO apply).

Hope this helps!

[Palmaker]

He sboari, sorry for the delay in my reaction but I used the holidays to go back to “school” to do a serious Typescript course. I will go through your tips and will come back, but thanks for your help well appreciated. Lex

[Jamie]

Hi @Palmaker, it is very interesting to see that you can combine multiple object types and use the result as data input for the Pivot table widget within Workshop. From what I understand, you are feeding the output from the function, which has type “FunctionsMap”, into the data input of workshop’s pivot table. However, when I was trying to use a function that outputs a FunctionsMap (which is working properly when used to derive function backed column) and feed it into workshop’s pivot table, it did not work for me and instead showed this error: “Unsupported function return type: map”

I might have missed something here, and it would be great if you could please provide more details on how you configured the pivot table to accept multiple object types and their aggregations.

Thank you!
Jamie

[Palmaker]

Hi Jamie,
We are currently working on a project to replace SAP BW with Foundry. This requires creating hundreds of pivot tables that leverage multiple linked Object Types. Palantir has developed a beta capability for us, allowing derived properties in Object Type A to be sourced from Object Type B. However, this beta feature currently supports only a single link, whereas our use case often requires two or even three links.
Additionally, we’ve encountered a limitation: the Filter widget does not work when linked Object Types lack a shared ID that the filter operates on. This led me to raise a question about the related function, where sboari provided significant support.
My remaining question is: where and how should this function be implemented in the Filter widget?
I hope this clarifies the context. Please let me know if further details are needed!
Best regards,
Lex

[Jamie]

I have to post another reply as I have a limit of sending 1 attachments per reply. Here is what I meant in Point Number 3 previously:

[Jamie]

Hi,

thank you for the additional details! In other words, the screenshot you showed is an internal beta version from Palantir which allows an “Object Set” type variable to represent a usual object set with its original properties + derived properties (coming from directly linked object types), is that correct?

From my experience, the Filter widget in Workshop does not accept typescript function output. Therefore, as a workaround, our team sometimes use this approach–

1. Define a variable (I will call it objectSetA here) of “Object Set” using the “Function” option, which e.g., can accept a list of project IDs as input, and in the function, multiple filtering and transformation steps can be used to output the properly filtered object set:
   

   

2. Define another object set which is the result of other filters from the conventional filters (if applicable) in Workshop’s Filter widget (I will call it objectSetB here)

3. End result: Define the final filtered object set by taking the intersection of objectSetA and objectSetB and use it as the pivot table’s input

I hope this helps somehow

Jamie

[Palmaker]

Hi Jamie,
Thank you for your reply!
Regarding your first point: beta version from Palantir which allows an “Object Set” type variable to represent a usual object set with its original properties + derived properties (coming from directly linked object types), is that correct?
Palmakers: Yes, that’s correct. PLTR currently support a single hop (one link), and PLTR is actively working on enabling multi-hop functionality.
As for your second point, we’re currently implementing your advice on linking the function to the Objects. Thank you again for your valuable help!
Best regards,
Lex

[Palmaker]

Dear sboari, Again thanks foryour help which was a big help and still a small question. I get the following error on your code: Static functions can not be published by functions-typescript/src/index.ts. Either remove the static keyword from the method in functions-typescript/src/index.ts or remove @Function/@OntologyEditFunction decorators from the static methods.

I hope you can check the code one more time and my question is: can I delete “static”?

Your help is appreciated.

import {ObjectSet,Objects,NlKostensoortProject, NlWerkorderBoeking} from “@foundry/ontology-api”
import {FunctionsMap, Function, Double} from “@foundry/functions-api”

interface getFilteredAggregations {
projectIds: string,
projectId: string,
projectName: string,
totalValue: number,
hierarchyId: string,
}

export class PivotTableFunctions {
@Function()
// Delete static??
public static async getFilteredAggregations (projectIds: string): Promise<FunctionsMap<NlKostensoortProject, Double>> {
// Fetch all ObjectTypeA (row groupings remain unchanged)
const aProjects = await Objects.search().nlKostensoortProject().allAsync();

    // Fetch filtered ObjectTypeB entries directly using projectIds and aggregate booking value
    const filteredBookings = await Objects.search().nlWerkorderBoeking()
        .filter(booking => booking.projectId.exactMatch(...projectIds))
        .groupBy(booking => booking.projectId.exactValues({maxBuckets: 2000000}))
        .sum(booking => booking.waarde);
    
    // Map results
    const mapProjectValues = new Map <string, Double>()
    filteredBookings.buckets.forEach(bucket => {
        mapProjectValues.set(bucket.key.toString(), bucket.value || 0); 

    });

    // Create a FunctionsMap to store the results of ObjectTypeA data with aggregated values
    const resultMap = new FunctionsMap<NlKostensoortProject, Double>();

    // Map results
    aProjects.forEach(project => {
        resultMap.set(project, mapProjectValues.get(project.kostensoortId) || 0)
    }); // Use `0` if no matching bookings
   return resultMap;
}

}

[Palmaker]

Sboari, no need to go through the code it works perfectly thanks to your contribution

[sboari]

Hey @Palmaker I hadn’t seen the notification and am just now catching up with a few messages!

Glad you got it working!

If no issues persist, please feel free to close the topic or mark it down as solved.

Thanks!

Best,

[Palmaker]

sboari, checked all the boxes but how do I close the post??

[VincentF]

Hey ! You can’t close the post really, but you already marked it solved, which is sufficient ! Thanks