Finding the previous record while creating a new record

[Clarkmcfly]

Hi, I have a problem accessing the previous record on a found set that I need to evaluate. For an inventory that I'm developing, I need to know the value of, say the field QtyIn recorded on the last transaction. Please see the image below:

http://www.designink.info/test/Case1.jpg

I tried using the function GetNthRecord ( QtyIn;Get ( RecordNumber ) - 1) to get the QtyIn and passed it on the QtyInPrevious field just to have a reference to see if it's doing what it's supposed to do.

You will also notice that on the left pane, the records are Unsorted and is displaying ALL the records created in the table.

The problem becomes evident when I tried narrowing the selection in a found set. It looks like this one:

http://www.designink.info/test/Case2.jpg

Note the value in QtyInPrevious does not match the intended QtyIn. I also noticed that when doing a found set, filemaker reports the following:

1. Current record position in the found set

2. Total records in the found set

3. Total records in the database

In order for me to calculate the remaining quantity in the inventory, I'll be needing the details from the last recorded transaction for a particular item.

To achieve this, what approach would you suggest that I can perform? Also, is there a way I could duplicate the filemaker "book pages" method of navigating via script, like going through the found set (records 1-4) out of the total 10 records created?

Thanks in advanced!

[Søren Dyhr]

First off why wouldn't genuine summaries work in this case?

Your approach is very wise if it's book keeping we're after, except for the use of GetNth( in a straight forward calc' field. You need to change the appoach to LookupNext( where the account/itemnumber as well as date is the relational key.

http://www.filemaker.com/help/FunctionsRef-316.html

This makes a stored value in each record showing the level/amount at the record creation, this means when ever you single out the latest record for what ever account will it carry the "level" you need to know.

There is a tiny detail you need to be aware of. every acount needs an initial record that haven't performed the lookup (opening levels), to prevent picking values from account just before.

Study this:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000334

--sd

[Clarkmcfly]

Thank you very much for your response. I will study the references that you provided.

[Søren Dyhr]

But some kind of knowledge as to why genuine summaries not are up to the task would very much appreciated. Since chances are that you're barking up the wrong metaphor here. What is your solution supposed to work like, if it's a spreadsheet ... are you indeed barking up a tree, slightly out of Filemakers realm!

--sd

[Clarkmcfly]

Actually, this problem has been brought up when I came upon across the development of an inventory system that I am designing. The whole description for it can be found here:

http://fmforums.com/forum/showpost.php?post/289654/

I already received a response indicating that the solution should be a transactional model approach. I am thinking of a simpler way and do away with creating another table to record each transaction if I can be able to perform the tasks below:

FOR NEW TRANSACTIONS

1. Create a new transaction record

2. Check the transaction made for this item and determine the last remaining qty recorded

3. Compute for this record's remaining balance (either adding additional inventory or subtracting from the remaining) by referencing the results I obtained from #2

TO CHECK FOR REMAINING INVENTORY

1. Find the specific date where the user needs to know how much inventory they have on that date

2. Check with the calculated value

My client will be migrating from a manual system of inventory so I am assuming that their method for recording these transactions will be "recording what's on hand", meaning, there won't be any proper sequence of recording. They may even start from the current date, going back as far as they can, and have the system generate a report to indicate "holes" or unrecorded transactions that they can later look for.

What I'm trying to avoid here is to have the system summarize(from first record) all the recorded "Added Inventory" and "Subtracted Inventory" each time the user requests of a report for particular dates he is interested in determining queries such as "how much item do we have on this date?"

[Lee Smith]

[color:blue]Are you Double Posting?

Please do not double post in the Forum.

Lee

[Clarkmcfly]

No, I'm not double posting. I'm referring to the link on my previous post to further clarify the topic being discussed in this thread.

[Lee Smith]

If you need to refer to an Older Tread of yours, in order to clarify a your new question, then it probably needs to be a [color:red]Reply to the older question, and not a New Thread.

It is not unusual for a question to take several twists and turns before a complete answer is received to a question. As a rule, these questions are related, and should be part of the same Thread. Therefore, the whole story is in one place, and doesn't require the reader to bounce all over the Forum in order to see the complete picture.

I can merge the two topics if you feel this is the case.

Lee

[Søren Dyhr]

I already received a response indicating that the solution should be a transactional model approach.

Lee is right you're x-posting, it's for you to question Barbara's wise words if there is detail here and there in the reply you not are getting ... are we really supposed to trawl all post made by you to get some sense of direction and purpose?

--sd

Edited May 8, 2008 by Guest

[Clarkmcfly]

Excuse the x-posting, but the purpose of the inclusion on the previous thread was simply to re-iterate the whole question that I asked previously to correlate with the topic being discussed.

No deliberate violation is intended.

By the way, I hope you could still give light to my inquiry.

Thank you.

[Clarkmcfly]

Thanks for the information, Lee. Please be patient with me, because this is the only forum that I'm actually participating.

I'll just be adhering to the rules.

Thank you.

[Lee Smith]

No Problem.

Seems we got side tracked on this. Please let us know where you still need help.

Perhaps you could post a copy of your file, and we can look at that when answering your questions.

Lee