0

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

null
    • Fred
    • 3 yrs ago
    • Reported - view

    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.

    • fuchsia_carriage
    • 3 yrs ago
    • Reported - view

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

    • fuchsia_carriage
    • 3 yrs ago
    • Reported - view

    A better way to ask this question:

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

    • Fred
    • 3 yrs ago
    • Reported - view

    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.

    • fuchsia_carriage
    • 3 yrs ago
    • Reported - view

    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.

Login to reply

Content aside

  • 3 yrs agoLast active
  • 5Replies
  • 674Views