Limit in related table lookup?

5 posts by 2 authors in: Forums > CMS Builder
Last Post: March 1, 2012   (RSS)

By rjbathgate - February 26, 2012

Hello,

I have the following:

-----
tbl_bookings
with a field "customer" which is the 'num' of a record in tbl_accounts
-----

When a booking is created, it is linked to the account record with this number.

Recently, it's stopped working when listing this field for the last few record (last in terms of ordering, not created)

e.g. I have a account with the surname "Zen", and a booking associated with this account. This booking is not recent, but is at the end of the lit due to sorting order.

The booking record does not show the name "Zen", but only the account number.

More recent bookings, which might have a higher surname (such as "Smith") work fine.

So it's not a broken code issue, but looks more like a limit in the number of 'labels' it can display.

There are 759 'bookings' and 1059 accounts, so not huge numbers....

In a backup installation which is 300 bookings and 500 accounts, it all works fine, displaying "Zen" name rather than number.

Any advice or thoughts, hugely appreciated.

Thanks
Rob

Re: [Jason] Limit in related table lookup?

By rjbathgate - February 29, 2012

Hi,

Thanks Jason, have sent 2nd Level Support Request,

Cheers

Re: [rjbathgate] Limit in related table lookup?

By Jason - March 1, 2012

Hi Rob,

I took a look into this and I learned something new about CMS Builder! :)

It turns out that cms builder imposes a limit on the number of options that can appear in a drop down list. By default, this limit is set to 999, which is why some of your options don't appear.

There are a couple of ways you can approach this. If your records are being populated by a front end form and not manually through CMS Builder, then this limit won't make any difference for you. The :label pseudo field will still get populated. So you could output the customer name like this:

<?php echo $record['customer:label'];?>

Take a look at the test page you put up to see this in action.

The other option would be to increase the limit from 999 to something greater. This can be done in cmsAdmin/lib/database_functions.php

In the function getListOptionsFromSchema() you'll see a line that looks like this:

$query = "SELECT `$valueField`, `$labelField` FROM `$selectTable` $where $orderBy LIMIT 0, 999";

You could increase this if you want. This is not the best option, however. If you get sufficiently large lists, you could slow down your page trying to load all the records. Also, since this would be a custom change, it would be overwritten as soon as you upgraded your version of CMS Builder. I would recommend using the customer:label option.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jason] Limit in related table lookup?

By rjbathgate - March 1, 2012

Hi,

Thanks! Good debugging :)

I've had to go for the increased limit option, as the problem occurs both front end and within cmsb admin itself.

It's working fine now, and the installation has a bunch of other custom stuff too so not a new problem re upgrading overwrites!

Thanks again, much appreciated,
Rob