Duplicate Records in a View

[JJMcM123]

I have 2 tables, one with product and second with multiple price updates for each product. I created a view to combine product and price but need to to show the most updated current price (and ignore all previous price updates). How can this be done in the view or a datapage based on view?

[Vitalikssssss]

@Ed727,

Since R30 Caspio version, there is an option available for Reports (List/Gallery/Tabular) that allows to show only "distinct" records.

I guess, this feature can easily resolve the case of topic starter.

https://howto.caspio.com/release-notes/caspio-30-0/

[MayMusic]

You can create a report based on your product table and then add a calculated field to the report page to read the most recent price for that product. For instance in the code below you are sorting the table by the date price was added descending and getting the first price from the PriceTable.

SELECT TOP 1 Price FROM PriceTable WHERE Product_ID = target.[@field:Product_ID] ORDER BY Date DESC

 

[JJMcM123]

21 hours ago, MayMusic said:

Price FROM PriceTable WHERE Product_ID = target.[@field:Product_ID] ORDER BY Date DESC

Thank you MayMusic. If I did correctly, it is putting newest price date at top, but still displays older prices. How can I have just the current price showing. The idea is that I have a price sheet and only want to display current price to client.

SELECT TOP 1 Price_Price
FROM Price 
WHERE Price_Price = target.[@field:Price_Price] 
ORDER BY Date desc

[MayMusic]

Since you want to get the latest price for a product , you need to have the product unique ID in WHERE clause.

 

SELECT TOP 1 Price_Price
FROM Price 
WHERE Product_ID= target.[@field:Product_ID] 
ORDER BY Date desc

[JJMcM123]

Thank you for your help. I'm still not having success though.  Is there a way to filter the prices with a view and then apply the results in a another view to combine with product?

[MayMusic]

Can you send me a screen shot of your table designs?

[JJMcM123]

[Vitalikssssss]

Hi @JJMcM123,

Try using the following expression in your Calculated field:

SELECT TOP 1 Price FROM Price WHERE PlanID = target.[@field:PlanID] ORDER BY Date DESC

Make sure that you using table "Plans" as a Datasource for your Report Datapage.

Hope this helps.

Regards,

vitalikssssss

[MayMusic]

First change the PlanID data type in your price table to be INTEGER as it is an Autonumber in Plans.

Then you can create a report based on Plan table, in report page add a calculated field and use the formula below:

If the Date field in Price table is a timestamp on submission then you can use the formula below:

SELECT TOP 1 Price FROM Price WHERE PlanID = target.[@field:PlanID] ORDER BY Date DESC

 

[JJMcM123]

Thank you both for your help. I was able to use the formula but only in a datapage report based on a Table. However I have 2 tables, one Product (Plans) that relates to multiple Prices in Price Table. I need to use a Report based on a View that combines details of Product (Plans) to the Price, but in View cannot filter One of each Product and One Price for that Product. I'm sure this application has been used before. Any ideas is appreciated.

Products 1714, 1785, 1829 shows more than one price:

Search again

City	SqFt	Story	Bed Rooms	Baths	Garage	Price	Price Date
Chino	1626	2	3	2.5	2	$423,880	08/18/19
Chino	1714	2	3	2.5	2	$441,426	08/18/19
Chino	1714	2	3	2.5	2	$454,426	07/09/19
Chino	1732	2	3	2.5	2	$431,880	08/18/19
Chino	1785	2	4	3	2	$458,770	08/18/19
Chino	1785	2	4	3	2	$469,770	07/09/19
Chino	1789	2	3	2.5	2	$433,880	08/18/19
Chino	1829	2	3	2.5	2	$461,990	08/18/19
Chino	1829	2	3	2.5	2	$466,990	07/09/19
Chino	1838	2	3	3	2	$482,470	08/19/19

Page     of 4

 

 

 

[Vitalikssssss]

Hi @JJMcM123,

Please send us a screenshot of table relationship between concerned tables.

Thanks,

vitalikssssss

[JJMcM123]

I have not created table relationships. Each table has id fields to related tables. Would creating table relationship links help with a view filter?

[Vitalikssssss]

@JJMcM123,

You should use Product table as a Datasource in order to create a report with a unique Product without duplicates.

You can use SQL in Calculated fields in order to retrieve values from Price table.

Relationships between table will not help to avoid the duplicates.

Hope this helps.

Regards,

vitalikssssss 

[alexm72]

The real answer is Caspio should just create a distinct field button of some sort like they do in dropdowns when you can check Distinct Options and Im sure it would take them 20 minutes to do and everyone could use this!

[DefinitelyNot31337]

10 minutes ago, alexm72 said:

The real answer is Caspio should just create a distinct field button of some sort

I agree that a feature like this is really a must-have for the platform.

 

Meanwhile, for everyone's appreciation, we may also utilize Pivot Tables for the meantime. Add your distinct-to-be field in the Row, and your date for Values. Then set the summary to Max

Do note, however, that Pivot Tables' performance becomes terrible as the DataSource grows. Less that 10,000 should work fine.

 

 

[Ed727]

JJMcM123 - I am curious how you resolved this. 

I would have tried to avoid this issue by having the datapage search only the Products  table (where each product is listed only once), and in the Product table you connect each product to only the most recent price.

If you want the ability to show historical prices then that's when you'd go to the Price table, for example to produce a list of prices per Product.