Get the difference in users between two fields

Get Help
1

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?

2

Relevant, might help:

2 Likes
3

Related:

4

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.