Help with Referencing details

Martina_Hawkins · 04-21-2020 02:09 AM

I have a table called Equipment List with fields
Item Id - Key
Category,
Description
Current Location
etc

I have a table Calibration Checks with fields
Calibration Ref No - Key
Item Id,
Calibration Date
Expiry Date
etc.

An item can have several Calibration records.
I have a VC in Equipment List called Calibrations with formula
REF_ROWS(“Calibration Check”, “Item Id”)

This works fine and shows a list of Calibrations in the Equipment List Detail View.

I have a view showing Expired Calibrations
Item ID
Calibration Date
Expiry Date
etc.

I want to link the Equipment List to this view so I can see the Equipment Description but because they have 2 different keys this won’t work.

Is this possible?

Suvrutt_Gurjar

@Martina,

It sounds that you wish to show records with expired calibration dates in the calibrations checks table with added information of equipment description and may be even category and current location.

It also sounds that the calibration check table references the equipment table. If so, then is it possible that you can pull the equipment description ,category , location etc. in the child table through dereferencing?

Alternatively, you may have the latest calibration expiry date of an equipment computed in the parent table and display the view in parent table itself?

Martina_Hawkins

That is what I want to do but need help in getting it to work please.

Suvrutt_Gurjar

In the parent table , you may have a VC called say [Latest Expiry Date] with an expression something like

MAX( [Related Calibration Checks][Expiry Date])

here the column [Related Calibration Checks] is the reverse reference list type column with expression REF_ROWS(“Calibration Check”, “Item Id”) that you mentioned.

Then you may have slice on the parent table with the desired columns included in that slice and with filter expression for that slice something like

[Latest Expiry Date] <=TODAY()

Edit: You may base the view for the expired calibrations on this slice

Martina_Hawkins

This is what I want the view to look like

Where the Equipment Details section shows the Description of the item referenced by the Item Id

Suvrutt_Gurjar

Then you may pull the columns such as Description and category etc.from the parent table in the child table through dereferencing expressions.Thus to pull equipment description in child table you may have an expression something like below in [Description] volumn of the child table

[Item Id].[Description] where [Item ID] is the reference type column referencing the parent table.

Now in the child table you may have a VC called say [Latest Expiry Date] with an expression like MAX(SELECT(Calibration Checks [Expiry Date], [Item ID]=[_THISROW].[Item ID]))

You may then have a slice on child table with a filter condition like
Latest Expiry Date] <=TODAY()

Please base the expiry date equipment view on that slice.

Martina_Hawkins

I already have a slice with this row filter which works great to get the Expiry date

Suvrutt_Gurjar

Great, that is exactly the slice filter expression.

Martina_Hawkins

I also have a VC called Calibration Expired

I still don’t understand how to pull the description from Equipment List into the Calibration View

Suvrutt_Gurjar

Is it correct that Calibration Check table references Equipment table? If so, could you add a [Equipment Description] column in the Calibration Check table with an expression like [Item ID].[Description] where [Item ID] is the reference type column in the Calibration Check table and [Description] is the description column in the parent equipment table?

Martina_Hawkins

Ok I figured out if I replace
REF_ROWS(“Calibration Check”, “Item Id”) with
[Item Id].[Item Description]

I can pull in the Description so I will need a VC for every Column I need to pull in from Equipment List

Suvrutt_Gurjar

Great, yes.

I mentioned it in the earlier post.

Also , you may not need VC depending on how the app operates . You may have real columns for dereference columns as well.

Martina_Hawkins

thanks for your help @Suvrutt_Gurjar.

this solution will work but I don’t understand how I am able to use Ref_Rows to show Calibration details in the Equipment View but cannot show Equipment Details in the Calibration View.

Suvrutt_Gurjar

Thank you. Good to know that the expressions work per your requirement.

As per my understanding , the difference is parent and child table relationship ( one to many).

One parent record can have many children records. That is why Rev_Ref column is there in the parent table. That column is a list type column and holds references (keys) to all or multiple children records associated with that parent record.

On the other hand a child has only one parent record. So multiple reference columns for the same parent table in the child table are not necessary or even feasible. Reference column in the child table holds reference to the single parent record key as there is only one parent.

Onec this parent record key is availabe in the child record, one can pull other columns from the parent by using that referenced key and by using dereference expressions.

Just in case you have not referred, I believe you may wish to always refer to the “Order Capture” sample app that has best examples of referencing, dereferencing.

https://www.appsheet.com/samples/An-app-for-managing-customers-products-and-orders?appGuidString=245...

Hope this helps.

Martina_Hawkins

Thanks for all your help @Suvrutt_Gurjar, much appreciated.