Get the difference in users between two fields

[nvee]

I have 2 databases in a space, Sessions and Inventory. Each one has assignees. Sessions have one Inventory, and Inventory has many Sessions.

I created a lookup column on Inventory to show all Session users for an entry. Now I’d like to create a new column which shows all users from Inventory assignees, which are NOT in the Session assignees.

I’m basically wanting a column to see which users have not been allocated to a session for a particular Inventory. Is there a way to do this with formulas? Or would I need to create a rule and do this with Javascript?

[ccollins]

Relevant, might help:

Generating an user list by subtracting other users lists

I need help with a formula for a button: I have 4 relation to users field in my meetings: Expected, Present, Informed absent and Non-informed absent. For the expected I already have a button that generate the list of expected people. Presents I usually add manually, so as Informed absent. Now I wanted to create a Generate Non-Informed Absent List that would take the Expected users list, subtract the presents and the informed absents and get to a result of Non-informed absent. For example, use…

[Chr1sG]

Related:

How to union two list fields in the same Database?

Can you provide some example use cases that need intersect or diff. It’s always interesting to hear what people want to achieve.

[nvee]

Thanks for all the help. I ended up using a script with the following (I changed Inventory to Available Hours):

// Developer reference is at api.fibery.io/#action-buttons

// Fibery API is used to retrieve and update entities
const fibery = context.getService('fibery');

for (const ent0 of args.currentEntities) {
    const availableHourEntity = await fibery.getEntityById('Sessions/Available Hour', ent0['Available Hours'].Id, ['Available', 'Id', 'Assignees', 'Booked Sessions'])

    if (availableHourEntity) {
        const sessions = await Promise.all(availableHourEntity['Booked Sessions'].map(session => fibery.getEntityById('Sessions/Session', session.Id, ['Assignees'])))

        const assignedIds = sessions.reduce((prev, session) => {
            const ids = session.Assignees.map(s => s.Id)
            return [...prev, ...ids];
        }, [])

        const availableUsers = availableHourEntity.Assignees.filter(user => !assignedIds.includes(user.Id))

        // First remove all available users
        await Promise.all(availableHourEntity.Available.map(user => fibery.removeCollectionItem('Sessions/Available Hour', availableHourEntity.Id, 'Available', user.Id)))

        // Then reassign available users
        await Promise.all(availableUsers.map(user => fibery.addCollectionItem('Sessions/Available Hour', availableHourEntity.Id, 'Available', user.Id)))
    }
}

Then I’ve just assigned that to the entity attach and detach on the Session and Available Hour.

[Mircea_Braescu]

Same need as OP.
We have Sharing Wednesday meetings where we enter the attendees.
We have a SW Feedback database where each team member can provide feedback for that meeting.
We know who attended, we know who provided feedback, I want a list of who is pending.

I solved this with workarounds by converting the names to strings and processing them as such, but it’s not a very elegant solution