Jump to content
Sign in to follow this  

Need help with calculation

Recommended Posts

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.


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.


Share this post

Link to post
Share on other sites

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.

Share this post

Link to post
Share on other sites


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

Share this post

Link to post
Share on other sites

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

Share this post

Link to post
Share on other sites

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?




Share this post

Link to post
Share on other sites

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 by stan111

Share this post

Link to post
Share on other sites

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 by comment

Share this post

Link to post
Share on other sites

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?



Share this post

Link to post
Share on other sites

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



Share this post

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.

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.

Sign in to follow this  

  • Similar Content

    • By Rich
      Greets, all:
      I'm using this calculation--in a parent table in a field temporarily named 'Temp'--to grab course numbers from the course_number field in a related/child table:
      UniqueValues ( List ( ASSIGNMENT_COURSE::Course_Number__lxt ) )
      (I'm using this calc because the child table contains multiple records with the same course number; it's not a key field.)
      My intent is to use six buttons--in the parent table--where each button would grab an assigned row's value using the calculation above. For example, say that the above calc has this result:
      I want Button 1 to grab AST-100; Button 2 to grab EDU-100; Button 3 to grab EDU-300, etc.
      Mind you, I can't make a value list out of Temp because it can't be indexed.
      TIA for your help! 
    • By Asu
      I know. Don't use them. But There is a simple task and a repeating field would be a quick solution.
      Field 1: a number
      Field 2: a calculated repeating field, each repetition adds the repetition number to the value of Field 1.
      Can it be done?
    • By Asu
      Hello FM Mavens, 
      I am likely missing something. 
      I have a script step that calculates the dimensions of an image in a container field. There are many such fields so the script should work with any arbitrary one. 
      I get the name of the field by using script parameter Get(ActiveFieldName). 
      Let's say I have a container field "cont1". The script is triggered by entering the field.
      I am trying to calculate the height of the image by using 
      GetContainerAttribute (Get ( LayoutTableName ) & "::" & Get ( ScriptParameter ); "height" ) The results are question marks - I am not sure why. If I explicitly identify the fields then the expression works. 
      Tried the same expression (Get ( LayoutTableName ) & "::" & Get ( ScriptParameter )) in the script step "Set Field by Name" and it worked, which means that the expression correctly identifies the field the script is attached to. Then why is it not getting it the first step? What's wrong with the calculation? (FM17)
      (I hope I posted it in the correct section)
    • By ash1474
      Hi, I am using this calculation in portal which calculates days in portal  Date - (GetNthRecord ( Date ; Get ( RecordNumber ) - 1 ) )
      how can i modify or do something else to avoid (?) in the first row.  Sample file attached for any modification/help.
      Thanks for any help
    • By Quant
      Could someone help me with this question?
      I have a row in a portal in wich i would like to make a calculation: Bedrag= Cases*Prijs,  but if Hoeveelheid is filled in then it should calculate Bedrag= Hoeveelheid*Prijs.
      The fields are all numeric. The calculation should be possible on one row, in this example i have made it on two.

      Yours sincerely
  • Who Viewed the Topic

    2 members have viewed this topic:
    OlgerDiekstra  rwoods 
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.