eva wong wrote:

Hi eva,

You can Sort By  on date attribute to get the newest record on the table.

Amreen

so you want only latest record only?

then for that, you can use filters for getting  only the latest record.

thanks 

Amreen

Hi Eva,

Why not do the following?

• apply Group By to Food.Name,
• apply Group By to Amount and, 
• use Aggregate Function Max on Food.Rewdate

The resulting aggregate will return the name, amount and Rewdate for the most recent Food of each kind.

Eva,

If you don't want to display the Amount, then don't use Group By. But if you do want to display the Food.Amount, using Group By will only display the one in the record with Food.Rewdate equal to Max(Food.Rewdate).

Can you explain better what you are trying to show on screen?

If you want just the highlighted items of that Aggregate then it's what I suggested, Group By each of the attributes you need (Food.Name, Food.Amount, Food.ShopId) and then get the Max of Food.Rewdate.

Eva,

You have one extra Group By that is "ruining" your results: the Id attribute is, by definition, unique, which means there will be no grouping whatsoever. If you remove the Group of Id, you will get the desired result.

Eva,

Those informative entries in the 1-Click Publish tab are unrelated (they are telling you that you have deleted attributes in two different Entities, but the corresponding columns have not been deleted from the database - which is the expected behaviour of the platform).

As for why it is not replacing the big 1-Click Publish green button with the Open in Browser blue button... typically it happens when your module does not have an Default Entrypoint, if I'm not mistaken... On your 1-Click Publish tab, do you see a blue button labeled OPEN IN BROWSER, with a dropdown next to it? If not, you should still be able to right-click on your Web Screen (in the Elements Tree) and select the Open in Browser option there...

I'm sorry, I was looking again at your query, and my initial suggestion is all wrong, because GROUPing BY Amount will also make many of the rows unique...

I'm still trying to wrap my head around what you are doing and whether it is at all possible with your data-model.

Tell me, when you click on the food, what are you expecting to see?

eva wong

I suppose the problem is solved by now. But I don't see any answers here. So I am writing to hope and help others.

My best guess of the situation is: For each shop, for each product, you want to get the LATEST price.

If you can handle raw SQL, then write sub-query for that. For out system, the best option would be to redesign your tables: Always overwrite the old prices, and keep your history in another table.

If you can't do either, try the following:

Query all records, group by your shopID and productID, get max(rewDate).

Loop through the record, for each record, query your database again:

• In this query, filter the records by shopID, productID, and rewDate = max(rewDate)
• You should get only one record, this record has the latest price. add this record to a new list of record (local variable)

after a lot of research, this was the simplest solution I identified to return the last ID, thanks