Need help with a basic view

R

I'm having trouble figuring out to construct views. I am used to doing this via SQL. I have a Contacts table with a Products subtable. I want to query all records where the product is of a certain type and the most recent purchase date is after a certain date.

If I were to write this in SQL it would be:

SELECT * FROM Contacts JOIN Products ON Contacts.ContactID = Products.ContactID

WHERE Products.ProductType = 'Widget ABC' AND Products.PurchaseDate > '12/31/2020'

 

(actually, this has the potential to return multiple rows for the same contact, and I would only want the MOST RECENT purchase, but I can't recall the SQL syntax for that off the top of my head)

 

Thanks,

 

F

Hi there -

Just want to verify that you want to view the lastest product purchased on the Contact you are viewing?

let curRec := this; <- gathers all the data of the current record you are on

select Product where Contacts.ContactID = curRec.ContactID and PurchaseDate > '12/31/2020'

I hope this helps.

R

Where do I insert this code? Is it on the main view or a column ?

R

A better way to ask this question:

In Table View, how do I display only the most recent record from a subtable?

F

I've just come across the Order By feature myself so maybe something like:

let curRec := this;

let xProd := (select Product where Contacts.ContactID - curRec.ContactID) order by PurchaseDate

last(xProd)

or if your data entry is always chronological then your can order by Product.ID and that will always get you the latest one.

R

Discovered that myself. I came up with something very similar that appears to work:

let p := last(Products[ProductType like "Widget ABC"] order by 'Purchase Date');

(put that in a new column filter of the parent table)

Then display whatever properties of 'p' I am interested in.

 

Reply