HOW TO: List Options

Hi Rich,

Typically, you wouldn't store member_id as a separate field in the cmsb_tournament table. Instead, you should set up a foreign key (or related record) that links directly to the num field in the cmsb_member table. This approach avoids the issue of having to update two separate fields simultaneously (the member field and the member_id field in cmsb_tournament), which could easily lead to inconsistencies.

By establishing this foreign key relationship, any query on cmsb_tournament can use the link to cmsb_member to retrieve the member's details, such as first and last name, based on the ID stored in cmsb_tournament.member. This ensures that all member-related information is maintained in a single location (the cmsb_member table) and remains consistent.

Have you considered using a foreign key or related record field to reference the member in cmsb_member? :)

Hi Rich, 

Some more thoughts on this.  

Do you have two fields in cmsb_tournament?  And if so, can you just use one `member_id` with the num as the value and the name as the label?  

What's the end goal?  What are you trying to accomplish? 

Hi Dave.  The Foreign key work perfectly.  While I'm using the ID as the key, I can use SQL to create the text field to display (ie. 'firstame,lastname').  

thank you

Richb.

To give you more context.  I have 144 golfer members. I'm creating golf parings of 4 in each group.  I'm using a Foreign key to reference the golf member vs duplicating their information.  The Foreign key works great, but I received an error when trying to add the 64th member.  I'm thinking there is a DB limit, however, I'm reading there is 253 limit on MSQL tables.  Not sure what. 

Hi Rich, 

Can you send in a 2nd level support request?  We can take a look for you. 
https://interactivetools.com/support/request/

Thanks!

Hi RichB, 

I added some debug code to your install (and the next CMSB version) to show the error, and it is as you guessed: 

MySQL Error(1069): Too many keys specified; max 64 keys allowed

As an alternative, I set up paring.group16_player4 as follows: 

• Field Type: list
• Display As: pulldown
• List Options: Get options from MySQL query (advanced)
• Query: (your MySQL query) 

This should work the same way, but it doesn't strictly enforce database integrity rules like foreign keys. But it is a common way that people set things up.

Another option would be to table for paring_groups with one group per record and then display it with the "Related Records" field type.  However, that version will require an extra click each time you want to add a record and a bit more MySQL on the front end, as you'd need to query multiple tables.  CMSB is quite flexible, so there are often multiple correct ways to do things.  Starting with the simplest, easiest approach is often a good strategy.

Hope that helps!  Let me know any questions.

Perfect.  This works and I can now support all parings.  Thank you.