How to Run the script for each row in portal

[gullfounder]

Hi,

I am fairly new in the FileMaker. I was working with a script and encountered a problem. I am made portal in which there are 3 fields and there is button in the layout. As the button is pressed the script triggers. But it only performs calculations for first row in portal and ends. I want it to run for each row in the portal.

I am attaching the Script.

[Vaughan]

Read up about the "Go to Related Records" script step.

[gullfounder]

I think it will not help me. I have read your link. i can't understand it. I need more piratical example on it. Sorry i am new!

I have invoice table and invoice line item. When client has inputted all the required data and presses the Button. The script will trigger and it will traverse through each line item and update it corresponding enters in another Product Table. This is what i want to achieve.

[bcooney]

Sounds as if your goal is to update a stored qty field in Products (perhaps, On Order or QtySold?). This is best done using a transaction. It's not really a difficult concept but perhaps challenging for a beginner. How about starting with Todd Geist's tutorial (that I seem to link to daily!).

[gullfounder]

Yes bcooney. That is exactly what i am tiring to achieve. I am reading that link you have given me.

[gullfounder]

I am sorry. I couldn't understand it.Kindly help me get a grip on it. for now i want it to be very simple traverse through the line items and update the stock field in other table.

[gullfounder]

Here is what i have made. Kindly help me fix it. What is wrong with it!

Kindly help me pleas.

"

Go to Portal Row [First]

Loop

Set Variable [$id; Value:Sale Detail::_fkproductID]

Set Variable [$qty; Value:Sale Detail::Quantity]

Exit Loop If[$id=User Prefrences::null]

Perform Find [specified Find Requests:Find Records; Criteria:Sale Detail::_fkproductID: “== $id”] [Restore]

Set Field [Product::ProductInStock; Product::ProductInStock - $qty]

Go to Layout[original layout]

Go to Portal Row [Next]

End Loop

"

[bcooney]

I think it would be better if you tried to understand transactional scripting. I'm willing to answer your questions about this...tell me where it begins to get confusing.

[Fitch]

It would be worth your while to learn about transactional methods. At the same time, it's good for you to learn where you went wrong with your script.

The problem is that when you do a Find, you're going to exit the portal. The portal row you were on is not stored anywhere. So when you come back and go to the "Next" portal row, it doesn't know where to go. Try this:

Go to Portal Row [First]

Loop

Set Variable [$id; Sale Detail::_fkproductID]

Exit Loop If[ IsEmpty( $id )]

Set Variable [$qty; Sale Detail::Quantity]

Set Variable [$ROW ; Get(ActivePortalRowNumber)]

Go to Layout[Product]

Perform Find [_fkproductID: “== $id”]

Set Field [ProductInStock; ProductInStock - $qty]

Go to Layout[original layout]

Go to Portal Row [$ROW + 1]

End Loop

Now imagine you had a table occurrence of your Product table linked to Sale Detail. Then your script could look like this:

Go to Portal Row [First]

Loop

Set Variable [$qty; Sale Detail::Quantity]

Set Field [related::ProductInStock; related::ProductInStock - $qty]

Go to Portal Row [Next;exit after last]

End Loop

Not only that, but you could then be transactional, i.e., if any of the product records was locked for some reason, you could choose to log the error and/or revert the whole inventory update process.

[bcooney]

Thanks for chiming in, Fitch, your answer was exactly what I wish I had had time to write.

[LaRetta]

By writing to a single product field, you lose ability to know whether a line has written or not. You risk that it will be ran twice and if User adds a new Lineitem later, you will have to handle it separately. And if amount changes (or line is deleted) you will have to capture the before and after to calculate the amount to transfer or deduct.

Everyone's suggestions are great but I would suggest using an aggregate calculation instead (to know your balance) or even better, use an Inventory table where you can also manually enter purchases (even tied to a Vendors table), enter damaged products , and physical inventory readjustments. Writing to single product field also Doesn't tell you monthly totals so you will still need to aggregate the Lineitems when you need that information.

The best configuration would depend upon several factors but a hybrid of static and aggregate (summing only the current month) can keep even the largest systems quite fast WITHOUT RISK. At the close of month-end, like at 4 AM, run process which writes to monthly total when you can verify it works properly; no need to worry that values will change because that data should be locked during that month-end process.

If you wish, I can put together a simple example of what I mean.

ADDED. Also no worry of record lock because first step in month-end is lock the records with PostDate and verified success and then the inventory is written. Why aggregate the same data back through years every time you view it when it will never change? Data which is locked at month-end should only be calculated once.

Edited July 9, 2012 by LaRetta

[RodSierra]

or even better, use an Inventory table where you can also manually enter purchases (even tied to a Vendors table), enter damaged products , and physical inventory readjustments. Writing to single product field also Doesn't tell you monthly totals so you will still need to aggregate the Lineitems when you need that information.

If you wish, I can put together a simple example of what I mean.

I think a simple example of a true transaction based inventory system is needed here. I've always had problems with people referencing the tutorial technique above as an inventory transaction, although the error capturing is a good example, the use of an inventory field in a product table is a poor example of maintaining inventory. Transactional inventory for us is one record per transaction in an inventory table. I've never really commented on this in the past, as I do not have the time to "put my money where my mouth is" and write an example of how this is handled, then keep up with the thread. But I think its worth visiting here with a solid example of true transactional inventory control.

[bcooney]

Great thread, keep 'em coming. I too encourage the use of an inventory table, and just had time to point into the right direction but not to escort the OP there. I usually set statics to calcs which I believe LaRetta is suggesting as part of her approach. I point to Todd's blog as a starting point for understanding transactional scripting, not as the quintessential inventory solution.

[gullfounder]

Thanks guys. I have fixed my script and fixed the accidental second time update pressing button. Since many of you suggested the transactional process for inventory managing. I would really be happy if LaRetta assemble the little example for me. Because the blog that bcooney has suggested has passed over my head. But if i have very simple Example made on that system it would be a great help for me to understand it.