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?
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.
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.
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
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..
Start Date: 07/01/16
End Date: 12/31/16
Click the dropdown next to save, "Save and Add Another"
Start Date: 01/01/17
End Date: 05/05/17
Save and Add Another
Start Date: 05/06/17
End Date: 12/31/17
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.
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)."