Custom field to add together range in appraisal tab

Discuss using and your tips/tricks for CATraxx
Post Reply
Belinea2010
Posts: 2
Joined: Sun Dec 31, 2017 9:52 pm

Custom field to add together range in appraisal tab

Post by Belinea2010 » Sun Dec 31, 2017 9:57 pm

First may I say Happy New Year.


In Album View, double click on any album and on the Appraisal Tab there are the fields, Purchase Price, Current Value and Selling Price.

I have remaned the last two to Shipping Cost & Import Fees and I need to add a custom field that will add Purchase Price + Shipping Cost + Import Fees.

This will then give me the true total cost of album.

Despite spending many hours trying and looking through the forums I am unable to find a way of doing this - that is if it is possible at all.

I would be very grateful for any help or suggestions. :D

User avatar
DougWilliams
Site Admin
Posts: 235
Joined: Thu May 22, 2014 11:36 am
Location: Wisconsin, USA
Contact:

Re: Custom field to add together range in appraisal tab

Post by DougWilliams » Sun Dec 31, 2017 11:00 pm

Hello Belinea2010,
Happy New Year to you as well.
You can't automatically add up the those fields but you could do a couple of different things.
1) Periodically run a SQLUpdate query that would add and update the appropriate fields.
2) Create a report that calculates the fields and displays it on a report (though you wouldn't see it "live")
3) Create a customer XSL field so that the total shows in an Explorer screen.

I can help with the SQLUpdate query but there is a small issue with using a Custom field as the total.
In the database, Custom fields are a kept in separate table then the album table. If zero custom fields have data, the row is not created. To update the total in a non-existent row is not as easy as having the total in the main album table.
My recommendation is to switch the Shipping Cost to Custom 10, Import Fees to Custom 11 and use Current Value as Total Cost.

Here is the SQL Query to update the fields as per my recommendations.
REMEMBER TO BACKUP BEFORE TRYING THIS!!!

Code: Select all

UPDATE Album
INNER JOIN AlbumCustom ON Album.AlbumID = AlbumCustom.AlbumID
SET Album.[Value] = [Album].[Price] + [AlbumCustom].[Custom10] + [Albumcustom].[Custom11];
Let me know how it goes!
Record Collector
"You Mean I'm Supposed To Listen To 'Em?!?!?!?"

Belinea2010
Posts: 2
Joined: Sun Dec 31, 2017 9:52 pm

Re: Custom field to add together range in appraisal tab

Post by Belinea2010 » Mon Jan 01, 2018 5:19 pm

Hi Doug

Happy New Year.

I remember you from the original FNP forum so it is great to see you here.

Many thanks for the kind suggestion and made on NYE too :D

I will give it a go and let you know how I get on.

Many thanks and onward into 2018.

Kind regards

Kenneth

Post Reply