General critiquing creating a running total/inventory field

[Dr_Bob]

Hello gentlemen and lady-types,

 

Not sure if this is the appropriate place to put this since Im sure what Im trying to do can be done in every version of Filemaker, but we will start here.  Basically why I became a member here was to have my work critiqued by more experienced users than myself and hopefully find a way to collect a running total field based on some known information.  I have a working database set up in the way I think we will need it.  Not sure if I can attach it to this post so I will wait until told to do so.

 

My problem:

 

I have a field set up for "Part Number".  I would like to have a "Quantity" field that will total up all of the entries with the same "Part Number" and display that by each item in my List View after a "Find."  I can't see this being difficult and think I was fairly close once, although it didnt proactively work.  It would give the correct quanity when a new item was created but would not change the quantity field for a previously entered entry upon a "Find."  The "Part Number" and "Quantity" field are on the same Table, so I think a Relational join is not what Im looking for but is all everything references while doing a Google search.

 

Im not looking for a handout as I know that is a great way to incur member wrath, especially with "noobie" type questions such as this, but a friendly nudge in the correct direction or a link to some information would be fantastic.  As I said, I am more than happy to post my current DB so you could see exactly what we are dealing with but I doubt it will be needed do to the essence(assumed non-difficulty) of the question.

 

Please let me know what info you need that I havent provided.  Tried poking around the site to see if I could find a solution, but Im not really sure how to form my search criteria.

 

FM9

Windows 7 OS

[bcooney]

Hello Dr. Bob,

 

How about viewing this video from Todd Geist and posting questions. My goal is for you to see a typical inventory setup. The lesson about transactional scripting is secondary.

 

hth,

Barbara

[Dr_Bob]

Thanks Barbara and thanks mods for moving this to the appropriate location.

 

I think I am on to a way to do what I want but I need to tweak it a bit.  Ive created a Self Join to the table I want to display my "Quantity" field.  I also created two Calculation Fields as seen here:

http://help.filemaker.com/app/answers/detail/a_id/3071/~/creating-a-self-join-to-test-for-the-number-of-occurrences-in-a-file

Which allows a count of a field I call "Available"(text).  The Quantity Field calculates correctly, but it populates with whatever the current records state of "Available" is.  So, if a record is set to "No", it will total up all of the "No" records.  Or if a record is "Yes", it will total up all of the "Yes" records.  Is there any way I can set my "sum()" function to add just the ones with a predetermined data set?

 

Ive tried making a new field that autopopulates with a "Yes" answer and use that as a second field in the "sum() calculation but that doesnt seem to work unless Im doing it incorrectly.

[bcooney]

OK. Let's start from the beginning. What tables do you have in your solution so far? You mention the field "Part Number" and "Quantity." Typically, Part Number is a field in a table of Equipment or Parts, but I'd rather not make any presumptions.

[Dr_Bob]

Sorry to jump around like that.  Basically all I have for fields that Im concerned with are "Available" and "Quantity".  "Available" can only be "Yes" or "No."  Essentially, when Available is listed as "Yes" for a record, I would like it to state the number of overall "Yes" records.  It does this based on how it is set up via the link I gave, but when looking at a "No" record it also gives the count of every "No" record.  Im aware that based on the way this is set up, this is working the way it should, but I was just looking for a way for it to ignore any "No" record even when it is the promenent record you are currently looking at in Browse Mode.

 

Sorry that Im making this sound more complex than it is.  If the above isnt clear, Ill try my best to describe it in another manner.  I just think that Im looking for a function in which I can put some additional info and Ill be good to go.

[bcooney]

Why wouldn't Available be a calculation based on Quantity level?

 

I'm trying to help you with your design. Let me introduce you to the concept of a flag field. A flag field is a boolean field that looks at the data and results in "True" if the test posed is true.

 

flag_IsAvailable = Quantity > 0

 

Now, sum flag_IsAvailable.