Can I create a field which looks up data from another record within the same table?

  • 0
  • 2
  • Question
  • Updated 2 years ago
  • Answered

Hi there, 

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! 

Photo of Katy

Katy

  • 60 Points

Posted 3 years ago

  • 0
  • 2
Photo of Neal

Neal

  • 70 Points
Hi Katy,

Screenshot prototype of your requirement is attached.

Please provide feedback regarding it.

Thanks,

Neal

NealPatil @ gmx.com

Photo of Katy

Katy

  • 60 Points
Hi Neal,

Thanks for your reply.

Your screenshot shows my required end result - I just need to work out how to achieve it.

Katy
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Katy,

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.
Photo of Katy

Katy

  • 60 Points
Thanks Mark, I've managed to set up the new field which would allow users to select the previous tariff from a drop down menu of all tariffs which relate to the appropriate supply.

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?

Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
It is probably possible if the Key field of the Tariff table were constructed with a compound hyphenated key like [Related Supplier] - [End Date].

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.
Photo of Vlad

Vlad

  • 160 Points 100 badge 2x thumb

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.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,280 Points 50k badge 2x thumb
Vlad if you wanted to have a copy of the same field why not just make a formula field equal to the original field.

or is that what you are saying that you did?
Photo of Vlad

Vlad

  • 160 Points 100 badge 2x thumb
yeah, this is what I did and wonder how come I didn't start with that from the beginning (at the end of a long work day and probably the reason), thanks for asking ; )