Whats the Proper Calc for this

[sicSRT8]

cRemaining = FT on Roll - Sum ( PaperUsage::Front_1st_RollFT_used ) and (PaperUsage::Front_2nd_RollFT_used) and (PaperUsage::Front_3rd_RollFT_used)

Obviously this wont work. Im trying to get this to pull the remaining feet from all three rolls based on Roll_ID number. I think I have the relationships correct but cant get this calculation to work.

Its taking the remaining feet from 3 different fields and updating one item. 3 different fields exist on one record which seems to make this complicated for me but I need reports on every specific field which is why im doing this.

Is anyone able to help me with this I have been trying to get this to work for a couple weeks now sadly.

Thanks

Tim

Edited February 19, 2009 by Guest

[David Jondreau]

It's a little unclear what the set up is...Are the cRemaining and FT on Roll fields in the PaperUsage table?

If so, try the following calc:

Let([

used = PaperUsage::Front_1st_RollFT_used + PaperUsage::Front_2nd_RollFT_used + PaperUsage::Front_3rd_RollFT_used;

start = FT on Roll;

result = start - used

];

result

)

[sicSRT8]

The field cRemaining only exists on the inventory table.

On the usage table I named them for each roll number that its calculating. EX: Roll_1Remaining, Roll2Remaining,...

If i used just one field as cRemaining for all the rolls each field would show the same calculated data which needed to be roll specific.

That calculation is ideally what I was trying to come up with (Not knowing how) but doesnt work and wondering if it now has something to do with relationship issue.

In relationships There are three seperate instances of how each Roll1 pulls the avail rollID number from inventory. By making three seperate relationships I was able to get each drop down show all the data but only populate that specific field. While at the same time when i pull my usage report it shows that Roll1 use was this... and roll 2 used was this and if roll 3 was needed was this... Also updating the inventory as well.

But if I use

RollFT - Sum (Paperusage::FF Start Roll FT Used) then i can get the result for that perticular roll. However on Roll2 and Roll3 it wont pull update the inventory on the drop down rolls selected for 2 and 3. I thought by using the formula you gave me would work but doesnt.

Usage Report has specific fields Roll_1 ,2,3,4,5, up to roll_6. I can only get roll1 to work and then I can see it in roll2 but past that im stuck.

Does that make sense? However you answered what I ask but still having the same issue with it not updating inventory

DJ thanks this has been a 2 week mystery for me... I know its somewhat complex but cant be that hard I just feel im missing something due to me not fully knowing the possible formulas for filemaker. Again thanks even if you cant answer I will just keep hacking at it and hope that i get it sooner than later.

[David Jondreau]

Your structure is still murky to me. You could try the following calculation. If that doesn't work, I recommend posting a copy of the file, so we can understand what's going on.

Let([

roll1 = Sum(PaperUsage::Front_1st_RollFT_used);

roll2 = Sum(PaperUsage::Front_2nd_RollFT_used);

roll3 = Sum(PaperUsage::Front_3rd_RollFT_used);

used = roll1 + roll2 + roll3;

start = FT on Roll;

result = start - used

];

result

)

[sicSRT8]

Sent you the link to the file.

[Lee Smith]

Why not attach it here (see File Attachments >> [color:orange]Manage files Below) to a [color:red]Reply? See how in the Help, Click on [color:blue]Help

>> Posting Questions >> Can I attach files to my post?

HTH

Lee

Edited February 20, 2009 by Guest

[sicSRT8]

Sorry here ya go. Rough Sample Layout but has everything linked how i thought would work but doesnt.

sampledb.zip

[David Jondreau]

Login info?

[sicSRT8]

admin

abc123

[sicSRT8]

any luck?