Add vs Overwrite - Relationship Creation Automation

[Maximilian_Breckbill]

Hello All - I have 3 databases: Candidates, Positions, and Matches. This last database is a relational DB with each record under Matches being the combination of 1 Candidate and 1 Position.

I’d like to eliminate the Match database, and relate Candidates and Positions directly to each other.

As I have thousands of Match records, I’d like to find an automated way of doing this.

I was able to create a Button formula that does this… almost:

[POSITIONS (SalesMaster)].Filter(Name = [Step 1 MATCHES].POSITION.Name)

However, I have the problem that many candidates are linked to multiple positions via Match records, and this automation overwrites any positions the candidate was previously directly matched to.

I’d like to find a way to ADD new position relations to each Candidate instead of REPLACING the ones that are already there.

I tried this:

[POSITIONS (SalesMaster)].Filter(Name = [Step 1 MATCHES].POSITION.Name)

• [Step 1 MATCHES].CANDIDATE.[POSITIONS-relational]

But it doesn’t allow me to complete the formula

Any tips on how to resolve this?

[Chr1sG]

I’d suggest adding a (temporary) lookup field to get all Positions from a Candidate’s Matches.
Then run an automation to copy the values in that field into the Candidate’s Positions field.

[Maximilian_Breckbill]

Wow.

Next time I will ask before even starting

THANK YOU!

[Maximilian_Breckbill]

The Next thing I want to do is copy over all the text in the “Notes” text field from each Match record a candidate is associated with to the Candidate Record.

Any idea how to best go about that?

[Chr1sG]

I would suggest a button automation on the Candidate db with the following script:

const fibery = context.getService('fibery');

for (const entity of args.currentEntities) {

    const entityWithMatches = await fibery.getEntityById(entity.type, entity.id, ['Matches']);
    const matchIds = entityWithMatches['Matches'].map((m) => m['Id']);
    const matches = await fibery.getEntitiesByIds('Space/Match', matchIds, ['Notes']);

    let summary = '';
    for (const match of matches) {
        const note = await fibery.getDocumentContent(match['Notes']['Secret'], 'md');
        summary = summary + '\n\n' + note;
    }

    await fibery.setDocumentContent(entity['Summary']['Secret'], summary, 'md');
}

(I have made guesses as to what the field names might be, and the space name is almost certainly not ‘Space’ but I hope you will be able to adapt it)

[Maximilian_Breckbill]

Amazing. This would have taken me forever.

As I’m not as well versed in Fibery code as I’d like, what are the field-names which you guessed that I would need to replace?

[Chr1sG]

const fibery = context.getService('fibery');

for (const entity of args.currentEntities) {

    const entityWithMatches = await fibery.getEntityById(entity.type, entity.id, ['Matches']);
//                                                                                  ↑
// 'Matches' needs to be replaced with the name of the field that links the candidate db to the match db

    const matchIds = entityWithMatches['Matches'].map((m) => m['Id']);
    const matches = await fibery.getEntitiesByIds('Space/Match', matchIds, ['Notes']);
//                                                   ↑     ↑                   ↑
// 'Space' needs to be replaced with the name of the space that contains the match db.
// 'Match' needs to be replaced with the name of the match db
// 'Notes' needs to be replaced with the name of the rich text field in the match db

    let summary = '';
    for (const match of matches) {
        const note = await fibery.getDocumentContent(match['Notes']['Secret'], 'md');
//                                                            ↑
// and again in this line

        summary = summary + '\n\n' + note;
    }

    await fibery.setDocumentContent(entity['Summary']['Secret'], summary, 'md');
//                                             ↑
// 'Summary' needs to be replaced with the name of the rich text field in the candidate db where you want the notes to be collected together
}

Hope that helps!

[Chr1sG]

Sorry I wasn’t clear.
You need to use the code I pasted in a ‘Script’ action, not in the ‘Update’ formula: