I'm new to this so bear with me....
I have a table for "Supplies", and a table for "Tariffs". Each supply can have many tariffs (but only one is "live" at any given time).
Is there a way to add a field to my Tariffs table which would provide detail from the previous tariff?
So for example, Tariff 1 might be for the period 1 Jan 15 - 31 Dec 15 and be provided by "Supplier A", and Tariff 2 would be for the period 1 Jan 16 - 31 Dec 16 and be provided by "Supplier B".
In the record for Tariff 2, I would like there to be a field called "Previous Supplier" which for this example would provide the value "Supplier A".
Any thoughts / advice appreciated!
QuickBase allows a table to be related to itself. So just set up that relationships and you will be able to chose your previous supplier and get any lookup values from the previous record. The only trick part is really in naming the fields so it is clear to yourself and the user when they are selecting when they choose the previous Tariff. QuickBase will give a name like [Related Tariff] that you will need to rename.
Is there any way to create a field which would automatically populate the correct previous tariff? I would rather avoid users having to select it manually if at all possible.
I was thinking something along the lines of matching the current tariff start date with the previous tariff's end date (+1 day).
So if the current tariff has a start date of 1st Jan 16, the field would automatically find and populate with the tariff (for the same supply) which has an end date of (1st Jan 16 - 1 Day = 31st Dec 15).
Do you think this is feasible?
ie it would need to be a key field that could be calculated. so that the record entered with a date of January 1 would be able to do that subtraction of one day and construct the link back to the proper parent. The form would need to calculate the Key field as the Key field of a table may not be a formula field. So the form would need to calculate it.
Can you tell me what uniquely , in theory, defines a Tariff - ie is it that combination of Supplier and effective dates? Do you even know the end date at the time a new tariff record is created? Or do you only know the end date when you are about to have a new Tariff rate come into effect.
I needed something much more simple and was reading/experimenting establishing same table relationships without success (may be my records were not unique but did not have the time to troubleshoot root cause) for at least the last couple of hours.
What I wanted to achieve is to have a copy of the same field for the purpose of another report (did not want to rename the original field) only named differently, so I was trying a to build lookup and snapshot field(s) instead of what worked like a charm - a formula field referencing the parent field.
Posting it just in case others might be searching for something that simple.