List Issues handling Multi-Indexed tables...

[bugeyedcreepy]

Hello, Great to finally have Access as a User! Thanks for delisting fabrikar.com from sbl servers worldwide!

** Edit: I really should make this clearer...
Fabrikar configured with Two Lists, both filtered for an AssociationId, one list 'Members' is using Table 1 and it's linked via databasejoin to 'Actions' as Table 2. Members is using two primary keys, AssociationId and SubMemberId.

Now, a problem I've finally come to that's stumped me. I have an issue where data filtering works on a databasejoin however because there's two indexes in the table I'm using, multiple records have the same unique id (SubMemberId) - which is leading to a problem. When I view Table 1 (Members), it tells me correctly that there's 10 records against a particular Member record however when I click the link for Table 2 (Actions), it displays all the action records correctly filtered and linked for the member record - then it repeats them all again for every other member record with the same unique key despite other identical unique-key records being filtered out for both tables on SubAssociationId field... I've tried everything I can to work around it without rewriting the backend.

Ideas?

 

[cheesegrits]

I wasn't aware we were on any sbl's. I check every few weeks.

When you say "Members is using two primary keys", do you mean it has a composite PK in MySQL?

-- hugh

 

[bugeyedcreepy]

I wasn't aware we were on any sbl's. I check every few weeks.

When you say "Members is using two primary keys", do you mean it has a composite PK in MySQL?

-- hugh

Hey Cheesegrits! Yes, Multi primary keys in MySQL, thanks! On the sbl, I hadn't checked for at least a year or so, it might've been a fleeting thing for the few months I tried signing up...(?)

Edit: Just in case it helps, this is the two tables I'm working with - there's more but this is all I'm working on so far...:

MariaDB [eventism]> show fields from submember;
+----------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------------+---------------------+------+-----+---------+----------------+
| associationid | int(11) | NO | PRI | NULL | |
| submemberid | int(11) | NO | PRI | NULL | auto_increment |
| memberid | int(11) | YES | | NULL | |
| userid | int(11) | YES | | NULL | |
| username | varchar(24) | NO | | NULL | |
| firstName | varchar(24) | NO | | NULL | |
| lastName | varchar(40) | NO | | NULL | |
| birth | date | YES | | NULL | |
| gender | char(1) | NO | | M | |
| adminlevel | tinyint(3) unsigned | NO | | 0 | |
| timeStamp | bigint(20) unsigned | NO | | 0 | |
| billingassociationid | int(10) unsigned | NO | | NULL | |
| billingmemberid | int(10) unsigned | NO | | NULL | |
| billinguserid | int(10) unsigned | NO | | NULL | |
| lastact | date | YES | | NULL | |
| lastcon | date | YES | | NULL | |
| history | int(10) unsigned | YES | | NULL | |
| passport | text | YES | | NULL | |
+----------------------+---------------------+------+-----+---------+----------------+
18 rows in set (0.01 sec)

MariaDB [eventism]> show fields from subaction;
+---------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+---------+----------------+
| actionid | int(11) | NO | PRI | NULL | auto_increment |
| actionindexid | int(10) unsigned | NO | | 0 | |
| divisionid | int(10) unsigned | NO | | 0 | |
| eventid | int(10) unsigned | NO | | 0 | |
| forumid | int(10) unsigned | NO | | 0 | |
| groupid | int(10) unsigned | NO | | 0 | |
| associationid | int(10) unsigned | NO | | 0 | |
| subassocid | int(10) unsigned | NO | | 0 | |
| sessionid | int(10) unsigned | NO | | 0 | |
| memberid | int(10) unsigned | NO | | 0 | |
| submemberid | int(10) unsigned | NO | MUL | 0 | |
| newid | int(10) unsigned | YES | | NULL | |
| authid | int(10) unsigned | NO | | 0 | |
| authdate | date | YES | | NULL | |
| serial | int(10) unsigned | YES | | 0 | |
| datestamp | date | YES | MUL | NULL | |
| expiry | date | YES | | NULL | |
| ref | varchar(24) | YES | | NULL | |
| payload | varchar(255) | YES | | NULL | |
| timeStamp | bigint(20) unsigned | NO | | 0 | |
| printed | tinyint(1) | NO | | 0 | |
| iic | tinyint(1) | NO | | 0 | |
| uc | tinyint(1) | NO | | 0 | |
+---------------+---------------------+------+-----+---------+----------------+
23 rows in set (0.00 sec)

 

[cheesegrits]

Yup, we don't handle composite keys. Sorry.

-- hugh

 

[bugeyedcreepy]

Yup, we don't handle composite keys. Sorry.

-- hugh

ooh, Bugger!
-_-
Not to worry, I'll spoon their relevant data into separate databases then.... Thanks anyway.

 

[JohnFox]

Hey guys,
do you still dont provide composite PK?
I now use the "areuniquevalues plug-in", thats ok, but real PK would be nice...