Advice on many-to-one relationship

[neznein9]

I have data in two tables that share similarly structured but distinct data. Both tables have a primaryKey field that's set to be unique, as well as a name and status field that have no validation. All three fields are set to index:All. There should be no overlap in the keys between the two tables but the names and statuses can be whatever.

 

In the join table, I have a foreignKey field that is set to be unique, and corresponding fields for name and status. When I started, I linked one of the data tables into the join table by connecting all three fields and allowing the data table to create records in the join table. This was working great until I added the second data table.

 

Now, things mostly work but sometimes when I modify the name or status of a record, it creates a second record in the join table - which should be unique based on the primaryKey and foreignKey fields but somehow it ignores the validation (no warning or anything).

 

I know this is unorthodox and should be set up differently but I have a huge amount of data already in spreadsheets that I need to import without disturbing the original structure. What I'd like is a join table that has the keys from the data tables (all unique) and then looks up the name and status from the linked record in whichever table the data originated from.

[bcooney]

I don't understand why you would have the name and status fields in the join table.

 

Can you offer a description that's less abstract. Please describe the actual data, the real names of the tables and give us a representative sample of the data.

[neznein9]

Thanks for the response - I'll try and sum up a bite-sized version of what I'm trying to do.

 

 

My intent was to keep the two tables clean and separate for now but I want to be able to view them and filter as though they were one set of data (sometimes). To that end - I added the join table, which (at least in my head) would allow me to group the data together without losing the ability to work with it discretely again later. It also lets me add more fields without messing with my import/export settings.

 

Here's an example:

 

 

This mostly works - but the records in the join table sometimes duplicate themselves instead of editing the existing linked record. What would be ideal would be cutting the links between the name and status fields and using a lookup to access those - except the join table doesn't know whether to look at alpha or beta for that data on each record.

[bcooney]

A join table resolves a many-to-many relationship. It does not serve as a "viewer" table into two different source tables. Why not import into the same table and create a "source" field to distinguish the Alphas from the Betas? Or use a virtual list to gather all the data into a "reporting" table.

 

Perhaps someone else, more clever than I, can see a way to achieve your goal.

[neznein9]

use a virtual list to gather all the data into a "reporting" table.

 

That sounds like what I'm trying to do...can you point me in the right direction about how to do that?

[bcooney]

http://www.mightydata.com/blog/taming-the-virtual-list-part-i/

[Priyabrata]

The problem would not occur if you make the relationship based only on the keys(primarykey and foriegnkey).

Removing the status and Name from the relationship.

And for the filtering the related records by Name and Status you could use global variables, You can use the global variables in the relationships for filtering the records.

 

If you need any help regarding the filtering of related records.

 

Free to ask...

[bruceR]

No, you cannot use global variables to filter the relationship.

[bcooney]

@Bruce- do you think this is a good use case for Virtual List?

[bruceR]

Could be. However, I don't really understand the problem to be solved.

[Priyabrata]

Hey Bruce

Is it we can't use or not possible to filter a portal using global fields?

[bruceR]

You are mixing up your language.  

 

Your original statement was about filtering a RELATIONSHIP using global VARIABLES.

 

No, you cannot do this.

 

Now you are discussing filtering a PORTAL using global FIELDS.

 

You can filter a PORTAL using global fields or global variables.

 

A relationship is only defined by fields on either side of the relationship.

[Priyabrata]

Sorry, Bruce for the miss communication.

I was actually inferring the relationship filtering as the Portal relationship filtering through the global Fields.

[bruceR]

I cannot understand that statement.