Possible to have a multi record section feed into another multi record section?

6 posts by 2 authors in: Forums > CMS Builder
Last Post: December 20, 2012   (RSS)

By weblm - December 18, 2012

We are doing a site for a ski property rental business. We have one multi record section to list each rental property.

Here's what I'm wondering if it's possible to do. I'd like to create a new multi record section for Seasons/Dates/Pricing. See, each property is priced by season. So for any given property, you will have the following for pricing selections:

Low Season: $price
(list of dates for low season)

High Season: $price
(list of dates for high season)

Spring Season: $price
(list of dates for spring season)

etc.....etc......


Now I could for the property record create a bunch of fields for each pricing season and have those be within the property record. However, I'd like the owners to be able to: A) create new pricing seasons and B) be able to change the dates for each seasons all at once.

So, is it possible to create a multi record section that has the fields: season_value, season_label, season_dates

This section would be dragsortorder sorted, so they can control the ordering.

Then, somehow tie this section to the property records....so when you create/modify a record, it first looks to this season section to get all the fields. Sort of dynamically building in the admin from another section. Now the only thing is....the price. See the season and dates are the same throughout all properties...but each property needs it's own price for that season. Also, some properties wouldn't have all the seasons.....so for example if a price for a season listed was blank, it wouldn't show on the display page.


Not sure I'm making sense.....but if anyone has any ideas, let me know.

Thing is, we love being able to make other sections populate other sections lists for example....that way the client doesn't have to get into the Section Editor to add a new entry. We'd love to be able to build something like this with these seasons/dates.

-Kevin

LM

By gregThomas - December 18, 2012

Hi Kevin,

It sounds like you already know how to link sections using a list field, but if not you can read how to do it here:

http://www.interactivetools.com/kb/article.php?Populate-a-list-field-from-another-section-15

I would created three separate sections, one for properties, one for seasons, and one for prices.

Season Section
In this section I would store the name of the season, and the date it starts and finishes.

Properties Section
In here I would store any property related information that you want to display on the page.

Prices section
This section would link up to the other two sections. I would have a dropdown list field that allows you to select a property, and another drop down list field that allows you to select a season and finally have a field for the price.

This should allow your customer to easily rename seasons, change the dates, etc for all of the properties at once.

Then on the main site details page for a property, you could retrieve all of the related prices for a property from the prices section, and then find the season that relates to each price.

Is this close to what you are looking for?

Thanks!
Greg Thomas







PHP Programmer - interactivetools.com

By weblm - December 18, 2012

Greg,

Thanks for the reply.....very interesting way to look at it. That's why I love these forums!!

Yes I know how to link sections....we do that now for things like reviews that we have them select a property.

I'm going to look into testing your way.....it definitely sounds possible. The only real issue is speed of updating. Every property will have about 6 different season rates......so with around 26 properties.....that would end up being 156 entries in the last Prices section.

BUT...this is very interesting and might just work.

Thanks so much!

-Kevin
LM

By gregThomas - December 19, 2012

Hi Kevin,

This isn't possible in the current build of CMS Builder, but it should be fairly simple to create a plugin that can do this. If you know how to create plugins, the hook you will needs is record_save_errorchecking.

If you require, you can send an e-mail to consulting@interactivetools.com and we can give you a estimate to build the plugin.

Thanks

Greg
Greg Thomas







PHP Programmer - interactivetools.com

By weblm - December 20, 2012

Greg, awesome thanks. I'll put together an email to send.

So I have the functionality working and wanted to post some of it here to see if there are better ways to do this.

To review I have the following sections:

- Properties
this section contains all the details about each new property. The customer manually enters the name)

- Pricing Seasons
this section has fields for Season name, season value (all lowercase no spaces to store in the DB), and the date range that season covers.

- Property Pricing
this section pulls in the list of Propert names from the Properties section and the list of Seasons from the Seasons seciton. We can then add a new entry for each pricing property/season combo

I then have the following DB calls at the top:


// load record from 'property_listings'
list($property_listingsRecords, $property_listingsMetaData) = getRecords(array(
'tableName' => 'property_listings',
'where' => " TRIM(property_name) LIKE '".mysql_escape($title_like)."'",
'loadUploads' => true,
'allowSearch' => false,
'limit' => '1',
));
$property_listingsRecord = @$property_listingsRecords[0]; // get first record
if (!$property_listingsRecord) { dieWith404("Record not found!"); } // show error message if no record found


// load records from 'pricing_seasons'
list($pricing_seasonsRecords, $pricing_seasonsMetaData) = getRecords(array(
'tableName' => 'pricing_seasons',
'loadUploads' => false,
'allowSearch' => false,
'orderBy' => 'dragSortOrder',
));

To display details on the property I use all the variables from $property_listingsRecord.


Now, to show the pricing details I do the following. Let me know if there is a better method:

<?php foreach ($pricing_seasonsRecords as $pricing_seasonsRecord): ?>

<?php
$priceRecords = mysql_query_fetch_all_assoc("SELECT price,pricing_note FROM {$TABLE_PREFIX}property_pricing WHERE season='$pricing_seasonsRecord[value]' AND property='$property_listingsRecord[num]' ORDER BY dragSortOrder DESC");
?>

<?php if ($priceRecords): ?>

<?php foreach ($priceRecords as $priceRecord): ?>

<?php echo htmlencode($pricing_seasonsRecord['title']) ?>
<br />
<?php if ($priceRecord['pricing_note']) { echo "<span style='font-size: 10px;'>".$priceRecord['pricing_note']."</span>"; } ?>

<?php echo money_format('$%i', $priceRecord['price']) ?>

<?php echo $pricing_seasonsRecord['dates'];?>

<?php endforeach ?>
<?php endif ?>
<?php endforeach ?>


I also need to grab the overall minimum and maximum price for each property. To do this, at the top I'm including these:

$minPrice = mysql_query_fetch_all_assoc("SELECT price FROM {$TABLE_PREFIX}property_pricing WHERE property='$property_listingsRecord[num]' ORDER BY price ASC");
$maxPrice = mysql_query_fetch_all_assoc("SELECT price FROM {$TABLE_PREFIX}property_pricing WHERE property='$property_listingsRecord[num]' ORDER BY price DESC");

Not sure if this is the best method or not.

I would like to say thank you again for helping. This is awesome....and is making this project be that much better.

-Kevin

LM