Many to Many Relationships - What am I missing

Ask for Help
1

Hi fellow gliders!!

I need some help setting up a many to many relationship.
I am sure I am missing something small that is causing a blockage.
I am using Glide tables.

Here is the scenario…

Two tables - one has a row for each job advert that has been posted
The second table has a row for each applicant that has responded to the adverts

I have created a relationship successfully where each job advert is related to a number of candidates - and that is a ONE job order to MANY candidates relationship
The source for that collection is Rel Advert to People - That shows the advert details and a list of candidates that applied for that role.

But I also have the reality that one candidate can apply for many jobs… and I do not seem to be able to get that working. I am sure I am missing something here

Here is a mock up of the CANDIDATE table

ROwID (Candidate)
Candidate Name
AdvertReference (FK) (Text field into which the RowID ( Job Advert) is inserted)
Rel People to Advert CANDIDATES (Job Advert Reference) matches JOB ADVERT (Row ID)

Here is a mock up of the JOB ADVERT table

RowID (Job Advert) RowID of Job advert
Job Advert Reference The unique Job Order reference
Rel Advert to People. RowID matches CANDIDATES_AdvertReference FK
Advert heading details Job Name
Advert date posted Date advert posted

This gives a one-to one relationship. I want a many to one relationship where one candidate can have may Job Adverts

I want to be able to search for a candidate and see a related list of all the jobs they have applied for over the life of the system. (Yes many job seekers apply for more that=n one application)

I think The relationship that is causing the problem is CANDIDATES (Job Advert Reference) matches JOB ADVERT (Row ID) because it only seems to work one to one - Not one Candidate to Many jobs

It works fine if there is only one advert per candidate but as soon as a second job advert is added to the CANDIDATES (Advert Reference) filed, the relationship no longer works

Any help greatly appreciated - I have been trying to work this out for a week

2

Actaully I just saw an article from @Jeff_Hager back in Nov 22 where he suggests splitting the field with the multiple items into a split text field and then using that in the relationship definition - Let me try that now

1 Like
3

Well this worked -!!!

I have now made the relationship on the split text field and it now lists all the job adverts that one person has applied for.
Gave myself my own answer by laying out the problem

1 Like
4

Many-to-many relation: create a join table.

It’s not a must, but in most cases I do that. It’s easier to understand the database architecture that way and it prepares your app for future feature development.

5

Thank you @nathanaelb I will explore this option - but at the same time, I now have many-to-many working by simply using split text fileds in the relationship rather than text fileds with multiple keys separated by commas (which does not work)>

@nathanaelb, have you tried that approach?

1 Like
6

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.