Need help with a basic view
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,
5 replies
-
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.
-
Where do I insert this code? Is it on the main view or a column ?
-
A better way to ask this question:
In Table View, how do I display only the most recent record from a subtable?
-
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.
-
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.
Content aside
- 3 yrs agoLast active
- 5Replies
- 677Views