Need help with calculation

[stan111]

Gents, need your help on the following issue:

I need to track my expanses. There are 4 tables: Products, PriceChanges, Transactions.

The problem: I need to make a script which will make calculations in transactions tab, accounting for the latest price change.

Example:

01 jan17 I bought one bottle of wine for $1 

03 jan 17 I bought the same bottle of wine for $1,5. So the latest price is $1.5

In Products table there is one record which is wine. 

In PriceChanges table there are two records: $1 and $1.5

I need that Transactions table automatically take corresponding wine prices while adding new or past records.

 

[comment]

This is not a question about calculation. It is a question about relationships. You need a relationship between Transactions and PriceChanges, based on matching ProductID, and sorted on the PriceChanges side by Date, descending. This will ensure that the latest price is always the first related record in PriceChanges - and you can define the Price field in Transactions to lookup the value using this relationship.

[stan111]

Hi, 

thanks for the fast reply. Hmm...I am new user to FM, the solution sounds really confusing at the moment, but I'll try.

[comment]

I am sorry if I make this even more confusing, but I have just noticed that your original post says:

1 hour ago, stan111 said:

I need that Transactions table automatically take corresponding wine prices while adding new or past records.

To accommodate past records, you will need to base the relationship not only on matching ProductID, but also on:

Transactions::Date ≥ PriceChanges::Date

[stan111]

Comment,

I wasn't successful in performing your recommendations. Is it possible to check my file? Thanks.

Untitled Copy.fmp12

[comment]

See if this works for you (haven't had time to test it myself).

Untitled Copy 2.fmp12

[stan111]

Thanks, unfortunately it doesn't work for me

 

 

[comment]

It doesn't work because (a) I forgot to sort the relationship, and (b) you have the wrong Price field in the portal to Transactions. Try it now.

Untitled Copy 3.fmp12

[stan111]

you are the man! Thank you!😀

[stan111]

Hi Comment, 

I created a dashboard with a bunch of buttons, attached script to every button, which allows me navigating to specific record of the Products table. 

The process of creating a script to every button is a very time consuming as I need to write a script that  a) goes to layout b)finds the certain record 

1 "Step: Go to Layout
    Layout: Products

2 Step: Perform Find" 

 

Is there a way to simplify the task?

 

Thanks!

 

[comment]

This has nothing to do with your original question. Please start a new thread.

[stan111]

I've got a trouble, trying to incorporate additional field  into calculations at Transactions tab. I used CASE function in the formula.

The trick with lookup prices doesn't work now. 

Would you please help me out with that? 

 

Edited November 30, 2018 by stan111

[comment]

I will gladly answer any questions you might have - if I find them interesting. I will not debug your file, sorry. Maybe someone else will be able to help you.

Edited November 30, 2018 by comment

[stan111]

that's fine. Let's go that way:

 

1. I have a Price and Item. I need to find Total.

Formula will be: Price * Item

2. I have Price, Item and different Sellers.

Formula will be: Price(of the specific seller)* Item

 

How can I realize this case in FM?

 

 

[comment]

Sorry, that makes no sense to me. Price is usually an attribute of an item. I don't understand the meaning of Price * Item.

Re #2, If an item is sold by multiple sellers, you will need a join table between Items and Sellers. This table will have an individual record for each Seller/Item combination, with a Price field holding the price of that specific item from that specific seller. There is nothing to calculate here, it's pure data (essentially a price list).

 

 

[stan111]

Thanks a bunch!