Jump to content
larrycfc

Help with calculated field

Recommended Posts

larrycfc

I have a database that I'm converting from an old fp5 version which had separate related files. It's a customer database that also had a transactions file, a last sale date file, and a sales ytd file. All of these are now tables within the new database, but I want to further simplify the structure. The most recent invoice date for a particular client can be found in the transactions table; I need help defining a function to search for it to place it directly on the main screen. I also have defined a total sales field as a summary within the transactions table, but want to find just the sales for the current year. I even tried If ( INV DATE > 12/31/2014; Total Sell; 0) but that doesn't work. There has to be a slick solution.

Share this post


Link to post
Share on other sites
AHunter3

a) for the most recent invoice date, SORT the relationship by Invoice date, descending order, and then Transactions::Invoice Date will be the most recent (maximum value in Invoice date).

 

b) For the sales for the current year, create a new table occurrence of Transactions (let's call it "TransactionsCurrentYear), create an unstored calc field in your local table (is that Customers?) defined as Year (Get(CurrentDate)) and also a calculation field in Transactions defined as Year (Sales Date) and then set up a relationship between Customers and TransactionsCurrentYear based on Customer ID = Customer ID (or however your current relationship is set up between Customers and Transactions) PLUS the two calculated "year" fields. That makes only this year's transactions related; now you can do Sum (TransactionsCurrentYear::Sales Amt) and that's your current year's summed-up figure.

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   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.




×
×
  • Create New...

Important Information

Terms of Use