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

Hi Katy,

Screenshot prototype of your requirement is attached.

Please provide feedback regarding it.

Thanks,

Neal

NealPatil @ gmx.com

Hi Neal,

Thanks for your reply.

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

Katy

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.

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

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.

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?

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 ; )