Jump to content
  • 0

Duplicate Records in a View


Question

15 answers to this question

Recommended Posts

  • 0

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

 

Link to post
Share on other sites
  • 0
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

Link to post
Share on other sites
  • 0

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

 

Link to post
Share on other sites
  • 0

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 Search again  
CityAscending 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  Next Last
 

 

 
     

 

Link to post
Share on other sites
  • 0

@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 

Link to post
Share on other sites
  • 0
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.

 

 

Link to post
Share on other sites
  • 0

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.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...