Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Previous Amount


Fliptophead

Recommended Posts

Hello, hello- I was so happy to find this site as I have been pulling out hair trying to figure things out on my own. Let me describe my familiarity with the program this way: two weeks ago I didn't even know what Filemaker was.

 

My current situation: I am creating a file to use for a vending business. It will track product flow and earnings. Quite simply, product is received, stored in inventory, and filled into machines. Money is collected and compared with what the software says "should" have been earned based on several numbers and calculations.

 

I have figured out the one end, that is how to receive product and add it into an inventory. Now I am dealing with the other end- putting product into machines. I have a table called MachineFills and another called MachineFillLineItems.

 

Each line item in MachineFillLineItems is for one specific product in one specific machine on one specific date. It includes fields for how much was in the machine at the last service (PreviousAmount), how much is being put in the machine (Fill), how many empty spaces there are (NegativeEndingInventory), how much is now in the machine (AmountInMachine) and how much sold since last service (AmountSold).

There are a few other fields but these are the main ones.

 

The AmountInMachine and AmountSold are computed based on simple calculations, which I have been able to figure out. The part I can't figure out is, how do I get the amount in PreviousAmount to automatically enter the number from AmountInMachine from the most recent service, when the MachineID and ProductID both match up?

 

Thanks in advance for your help!

Link to comment
Share on other sites

Define the relationship to sort by the date field descending. Then YourRelationship::AmountInMachine will refer to the most recent record's AmountInMachine value for the currently selected Machine/Product combination.

Link to comment
Share on other sites

Thank you for your response, Queue. It looks like it is more straight forward than I was making it out to be. And yet... I tried it and have not been successful. What relationship do I need to create to get the PreviousAmount field to show the AmountInMachine from the previous record? A self-join between the two? I tried it to no avail.

 

I have attached my file. I have shown the tables in question as green in the relationships graph. Do not venture forth if you are offended by ill-formed tables and questionable relationships!

Link to comment
Share on other sites

Every way I've tried it the PreviousAmount field shows the figure from the AmountinMachine field of the same record. I'm not sure how to have it get the figure from the previous record instead. And do I sort by Date descending on both occurences of the table in the self-join? Thanks again for your help and patience. I think this will all click with me at some point.

Link to comment
Share on other sites

I believe the relationship needs to be:

 

LineItems::MachineID = PreviousLineItems::MachineID

AND

LineItems::ProductID = PreviousLineItems::ProductID

AND

LineItems::Date > PreviousLineItems::Date

 

Sort the PreviousLineItems records by date, descending.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.



×
×
  • Create New...

Important Information

Terms of Use