Forum Discussion

sokatenaj's avatar
sokatenaj
Advocate II
9 years ago
Solved

Creating Relationships & Matching - Need some help

Hi Folks,   I have two tables. One table is all applicants and one table is filled/hires. I want to create a relationship between the two tables by the "Candidate Name" field, but unfortunately sin...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi sokatenaj,

    Additionally on my applicant table I have a field I that says "current status name" with a field that says "Linked". I want to say, if 'Applicant'[Name] that have a status of  'Applicant'[Current status name]="Linked" and that Applicant Name matches the name in 'Filled'[Name] then write MATCH, otherwise "Not Matched". 

    If I understand you correctly, you should be able to use the formula below to create a new calculate column in this scenario. :smileyhappy:

    Column =
IF (
    Applicant[Current Status Name] = "Linked"
        && CONTAINS (
            Filled,
            Filled[Candidate Identifier], Applicant[Candidate Identifier]
        ),
    "Match",
    "Not Matched"
)

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Microsoft Employee
9 years ago

Hi sokatenaj,

 

Could you post your table structures with some sample/mock data, and the expected result against the data? So that we can better assist on this issue? :smileyhappy:

 

Regards

sokatenaj's avatar
sokatenaj
Advocate II
9 years ago

Hi v-ljerr-msft,

 

Here you go. As a reminder, I cannot create a relationship because the candidate name and candidat identifier in the applicant table can show up 10 times if they applied to multiple jobs. So I need to think of a different way to validate that there is a "match". Hope this helps. Thanks so much!!!

 

Applicant Table

Job IDNameCandidate IdentifierPosting TitleDepartment NumberDepartment NameRecruiter NameHiring Manager NameCurrent Req StatusFirst Fully Approved DateLatest Cancelled DateLatest Filled DateCurrent Step NameCurrent Status Name
3042478Mouse, Mickey124555Cartoon CharacterCART123DisneyDisney, WaltBlah, BlahApproved6/28/2017  OpenNew
5448848Duck, Daisy128487Cartoon CharacterCART222ForestDisney, WaltBlah, BlahApproved6/28/2017  OpenAwaiting Response - Email
6545644Duck, Darkwing585456Disney DudeCART124DisneyDisney, WaltBlah, BlahApproved6/28/2017  OpenLinked
5998895Chipmunk, Alvin868677Music DudeCART222SevilleBagdasarian, RossHa, BlahApproved6/28/2017  OpenAwaiting Response - Email
3042478Duck, Darkwing585456Disney DudeCART124DisneyDisney, WaltBlah, BlahApproved6/28/2017  OpenAwaiting Response - Email

 

 

Filled Table

Job IDNameCandidate IdentifierPosting TitleRecruiter NameHiring Manager NameCurrent Req StatusFirst Fully Approved DateLatest Cancelled DateLatest Filled DateCurrent Step Name
1000877Mouse, Mickey124555Cartoon CharacterDisney, WaltBlah, BlahFilled6/28/2017 7/2/2017Offer Accepted
8878787Duck, Darkwing585456Chipunks BandDisney, WaltBlah, BlahFilled6/28/2017 7/2/2017Offer Accepted
  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi sokatenaj,

    Additionally on my applicant table I have a field I that says "current status name" with a field that says "Linked". I want to say, if 'Applicant'[Name] that have a status of  'Applicant'[Current status name]="Linked" and that Applicant Name matches the name in 'Filled'[Name] then write MATCH, otherwise "Not Matched". 

    If I understand you correctly, you should be able to use the formula below to create a new calculate column in this scenario. :smileyhappy:

    Column =
IF (
    Applicant[Current Status Name] = "Linked"
        && CONTAINS (
            Filled,
            Filled[Candidate Identifier], Applicant[Candidate Identifier]
        ),
    "Match",
    "Not Matched"
)

     

    Regards

    • sokatenaj's avatar
      sokatenaj
      Advocate II
      9 years ago

      Hi v-ljerr-msft,

       

      This was exactly what I needed. Thanks so much! 

      • sokatenaj's avatar
        sokatenaj
        Advocate II
        9 years ago

        v-ljerr-msft,

         

        Is there any way to add a date criteria to this? I tried multiple ways in the logic and kept getting syntax errors. I want to also add to only show up as a match if the "Latest Filled Date" is 6/1/17 or later.