How can I account for billing rate changes over time?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

I have a table listing Classes and a table with Class Pricing. Prices change over time, so I have an 'effective date' for class pricing changes.

How can I setup a relationship between the two tables so that each listing in the Classes table will calculate the correct Class Pricing based on the class end date?

Simplified example:

TRAINING-101 classes - 8/1/16, 10/3/16, 12/5/16, 2/6/2017

Class pricing -
$75, effective 7/1/2016.
$85, effective 10/1/2016.
$95, effective 1/1/2017.

Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Scott

Scott

  • 334 Points 250 badge 2x thumb
Hi Jen,

This is relatively easy to do.  I can help.  Could you write me back so that I know you're still interested/need help?  Let me know if class pricing can be applied to multiple classes, or if you want each class to have its own set of class pricing.

Scott
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
Hi Scott,

I'm still interested and need help. :smile:

Class pricing can be for multiple classes, but I think I'm going to have to put in an entry in the table for each course code and match between the two tables with COURSECODE_VENDOR.  There's an additional complication to be solved - pricing rates vary depending on who is teaching the class.
Photo of Scott

Scott

  • 334 Points 250 badge 2x thumb
Hi Jen,

The classes table is simply a dictionary of all available classes for enrollment, correct?
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
Correct. It's all classes available with their date and instructor.
Photo of Scott

Scott

  • 334 Points 250 badge 2x thumb
Jen,

Here's what your table layout should look like:

Table #1 - Classes
This table will house all of the classes by teacher with their current prices.

Table #2 - Class Prices
This table will house all of the class prices.

---

To start off, we'll need to link these two tables together.

Navigate to the settings page on Table #1 and go to "Table-to-table relationships".  We'll need to add a new relationship.  Select the first option, where Table #1 can have many Table #2's.  Continue hitting next until you've added the relationship.  

We'll now need to navigate to Table #2 and add some fields.  We will need to add the following:
Numeric - Currency:  "Cost"
Date: "Start Date"
Date: "End Date"
Formula - Checkbox: "Active"

Navigate to the fields section on Table #2 and edit your "Active" field.  We need to create the formula.  Enter the code below:

If(Today()>=[Start Date] and Today()<=[End Date],true,false)


Now take a breath!  We're halfway done.

--

Now we'll navigate to the settings page of Table #1 and go to "Table-to-table relationships".  We'll go into the relationship we created earlier.  Locate and click the "Add Summary Field" button.  We're going to add a summary field that picks the correct class price for the class.

Check the "A summary of a specific field" box.  The first dropdown box, we're going to pick "Maximum".  The second, we're going to select the field "Record ID#".  In the next section, "Matching Criteria" we are going to pick our field we created "Active" from the dropdown.  Make sure it reads Active -> Is Equal to -> checked.  Click save at the top right, name the field "Active Cost".

Now we are going to create another relationship based on the field "Active Cost" that we created.  Create a new relationship.  Make the relationship so "Many Classes belong to one Class Price".  (The opposite selection of the first relationship we made).  Make sure to select "Active Cost" as the reference field for this new relationship.  Only add one other field, "Cost" to the lookup fields for this relationship.

We're done with relationships now!  

--

Now we need to setup the forms.  This will be up to you.

For Table #1, please make sure the fields "Active Cost - Cost" are included, along with the "Add class price" button & the "Class prices" in-line report.

For Table #2, ensure that Cost, Start Date, End Date, & Active are showing on the form.  Nothing else is required.

--

You may now start filling out the classes!

Create a new record on Table #1:

Assuming you have a "Class Name" field, put something like:  Mrs. Johnson - Economics 101

Then save.  

You'll then want to view the class you just created and "Add class prices" for the class.

Here's where you can enter the pricing schedule for the future..

Cost: 60
Start Date: 07/01/16
End Date: 12/31/16

Click the dropdown next to save, "Save and Add Another"

Cost: 70
Start Date: 01/01/17
End Date: 05/05/17

Save and Add Another

Cost: 80
Start Date: 05/06/17
End Date: 12/31/17

Save!

You're now done.  Navigate back to Table #1 and view the class you created.  You'll notice that the "Active Cost - Cost" will display $60, as todays current date is between 07/01/16 and 12/31/16.  Once the date enters another cost range, the price will automatically update.

Hope I have been helpful!  If you require further assistance, you can contact me personally via the email on my profile and wen can work something out.  

Scott
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
Thanks Scott!  This works perfectly and I've used it in two places.  :smile:
Photo of Scott

Scott

  • 334 Points 250 badge 2x thumb
Jen,

Glad I could help out.  Sorry for my long-winded explanation.  I tried to condense it the best I could!

Scott
Photo of Neal

Neal

  • 70 Points
Thanks Jen and Scott,

Sample Screenshot Prototype is  attached for Classes and Class-Prices.

Neal

NealPatil @ gmx.com
Photo of Jen

Jen

  • 82 Points 75 badge 2x thumb
Note for anyone else that uses this (or myself in the future):
If get the error "CopyMasterDetail does not allow looping relationships" when attempting to copy a record after using this, you need to go into Home > App Settings > App Management and create a Copy master & detail records button.  Click the Advanced Settings to choose which relationships to copy, and probably uncheck "Copy all descendants of the detail records (details of details)."