Need Script

[[email protected]]

Hi,

I know that this should be fairly simple, but when I'm on a Invoice line items, I need to run a script to do the following. The conditions in the field rules database ar not returning the values I need.

Simply, I'll I want to do is have a script run on a field within a browse is do this.

On the invoice browse field named Quanity, I need a check that does 5 things:

1 - make sure that the Invoice Items "Quanity" is <= the Product "qty_authorized" field
2 - additionally make sure that the Invoice Items "Quantity" is also <= Product "quantity_ in _stock" field

3 - if both conditions are true, then subtract the Invoice "quantity from the Product "quantity_ in stock"
4 - Add an entry to the invoice items that set's a Invoice "quantity_issued" field to the Invoice Quantity (for audit purposes)
5 - in the product table , increment the product_usage by the Invoice "Quantity " for the product for tracking later.

Has anyone written a script close to this ??

Thanks,

Ryland

[Bob Arbuthnot]

Re: Need Script

Hy Ryland,

Under Operations, check out Posting. I think that you can probably accomplish everything with one Post operation.

If you run into problems or questions, post back here and someone will jump in and help you.

Bob Arbuthnot

[[email protected]]

Re: Need Script

Thanks Bob, I'll give it a try this morning. Ryland

[[email protected]]

Re: Need Script

Hi Bob"

I tried an update and post operation on the invoice items, and for some reason, it is not taking the current row focus and updating just the "onsave" . It is updating all records

I'm just not getting it....

Any help with a sample script updating Product records from the Invoice item file will be appreciated.

Ryland

[Keith Hubert]

Re: Need Script

Hi Ryland,

Have a look at Events->Events for the browse.

The other thing to keep in mind is what will happen if you delete the row.

[Al Buchholz]

Re: Need Script

Hi Bob"

I tried an update and post operation on the invoice items, and for some reason, it is not taking the current row focus and updating just the "onsave" . It is updating all records

I'm just not getting it....

Any help with a sample script updating Product records from the Invoice item file will be appreciated.

Ryland

2 ideas

1. Use action scripting to call the posting operation from an event for the invoice items and select only the current record.

2. Have you tried a posting field rule instead of a posting operation? That will handle the question the Keith posed. ie when a record is deleted.

[[email protected]]

Re: Need Script

Hi Al,

I have tried both possibilities:

1 - in the product file, I crated a posting rule ior the invoice items.dbf linking the the table linking the source for product_id in both product and invoice items to add to a field in the product field "usage" to increment (add) from the number within the invoice items. Still no luck so far in getting that usage field to update.

Of course I have a demo of Tuesday, and feel like an idiot in not getting this to work.

[[email protected]]

Re: Need Script

Found a problem in the item lookup key, brinking up wrong productid number.......must be causing the problem...will keep ya'll posted.

Ryland

[martinwcole]

Re: Need Script

You can easily accomplish this with a script in field rules for the lineitems table at the cansaverecord event and ondeleterecord event. Personally, I have never found posting to be reliable. Not sure why, but the amounts will occasionnaly get out of whack.

[[email protected]]

Re: Need Script

Thanks, without opening the action script, do you happen to have an example I can use in order to use those events.

This is what I'm using on the cansaverecord event:
product.qty_in_Sto - invoice_items.Quantity

Ryland

[CALocklin]

Re: Need Script

Don't take this personally but you've done it again! You're yet another person to prove my adage that it's time to worry when someone relatively new to databases (I make that assumption based on your 47 posts) says, "all I want to do is" or "this should be fairly simple."

It's amazing how many times new developers and customers think the hard things should be easy and the easy things should be hard. (I once had a customer apologize profusely before asking me to do something that he knew would be difficult - he wanted me to move a button from the left side of the form to the right side. Another customer thought a $6,000 estimate I gave him should have been more like $250 and certainly not over $500. My $6,000 quote was probably on the low side.)

I would say that this is actually one of the more complex things you will ever need to do. Not the most complex but far from "simple".

This isn't "an operation" - think of it as 5 separate operations. That way it will be much simpler.

Your items 1 and 2 sound like field rules - sorta. They aren't the kind of field rules that would ever need to be re-evaluated because the "qty authorized" and certainly the "quantity in stock" are likely to change from time to time and the only time that's critical is when the record is actually being entered. Therefore, I would script these checks in the CanWrite Field event of the table - not in the browse itself. Doing this in the browse itself would be difficult to say the least. Doing it as regular field rules (vs the CanWrite event) might mean that they would get re-evaluated and marked as errors if you needed to run the re-evaluate field rules command later on. (No guarantees on the "re-evaluate" concern but I'm pretty sure it's legitimate.)

If items 1 and 2 are part of the CanWrite events then you will never get to the OnSave event unless they are true. Therefore you don't need to worry about whether or not they are true. When the record is saved, some Post operations should handle items 3, 4, and 5.

My biggest concern with this would be the possibility of some unforseen issue when (a) changing the quantity later, (b) deleting a record, or (c) where you would need to override the limitations. I can easily imagine a situation where someone orders something that isn't in stock today but you are sure it will be available tomorrow. Then what do you do? Tell them to call again tomorrow and risk losing the order? Just food for thought.

Edit: I have to agree with Martin. I seldom use post operations myself.

In the OnSave Record event (not the CanSave Record event because (a) it wouldn't really be justification for not saving the current record and (b) you are changing something in another table) you will need a much more detailed script. Something along these lines (this is just off the top of my head):

DIM SHARED orig_qty as N
IF tc.quantity = orig_qty
     END'
END
tc = table.current()
tp = table.open( "Product" )
tp.index_primary_put("Item_ID_")
tp.fetch_find( tc.item_idf )
IF tp.item_idf = tc.item_idf    [COLOR="Blue"]'JIC[/COLOR]
     tp.change_begin()
     tp.qty_in_sto = tp.qty_in_sto - (tc.quantity-orig_qty)
     tp.change_end()
END IF
tp.close()

The "JIC" means "Just In Case" - you'll see it all over my code where something isn't really part of the basic logic but is done "just in case" something else goes wrong. In the above case, you might want to add a message in an ELSE section to tell the user that the item ID wasn't found and that it might be necessary to rebuild indexes.

There are a couple issues with what you tried. First, as pointed out above, you are changing something in another table so you would have to put it in Change mode. Second, something that simple might work in a set but putting it in the field rules of the table would be bad practice because it would cause errors if you ever tried to save that record while not linked to the other table. And, I don't care if you say, "but it will always be linked to that table" because there's no guarantee that that statement will always be true in the future. Someone is liable to come up with a report or form at some time that doesn't need that link and then you will have trouble.

Second Edit:

Oops, even that isn't good enough. It would work the first time around but won't work if you change the quantity or delete the record. (Remember when I said this was NOT simple!?) You really need to set a shared variable in the OnChange event then compare the current quantity to this variable in the OnSave event. I edited the code appropriately.

Another Oops - I noticed that I typed END' out of habit but I'll leave it there and explain why I do it. I put the single quote on the end so I can do a text search for END statements without finding every END IF, END WHILE, END FUNCTION, etc. This is actually a fairly common search because it's important to make sure all tables are closed before ENDing a script. Alpha says they try to close them if you forget but I've found that it's not good to rely on that - sometimes it doesn't happen. There are also other "cleanup" things that might need to be done like clearing the status bar and closing "Please Wait" messages.

[[email protected]]

Re: Need Script

No offense taken.

I'm an old Progress and Cobol programmer (not GUI Based), so I'm not that well gifted at using all of the features, and just learning the Xbasic features. The trick is that I need to learn is when to use or not use pure scripting vs Xbasic.

Your suggestions make good sense. I suspected that it would not be that simple, buy was looking for a good jump start.

Thanks,

Ryland

[Tim Kiebert]

Re: Need Script

Cal, thanks, you saved me a lot of typing. I have been trying to find the time to respond to this over the last couple of days and now I just need to type 'ditto'. When I first read the initial post I thought to my self "wait till Cal reads that 'simple' comment" ;) A note on the code you posted. I think when you edited you left the line tc = table.current() in the wrong spot.


Ryland

Another issue I see in this process is the test for invoice line qty being less than the in stock value. Lets say you have a stock level of 6 for a product. The user enters a line for 5 of these items. 5 is less than 6 so the transaction proceeds. The stock level is now reduced to 1. The customer changes his mind and the user needs to change the qty to 4. If the canWrite event simply checks qty of 4 against the now current stock level of 1 this will fail even though we are actually going to increase the stock level.

I think you would need to use the difference in the before and after qty here as well (as Cal has already mentioned using it in the update part). Maybe in conjunction with the a_field_value system variable. If the new value is less then one can assume stock is being added back and just allow the transaction to proceed (adjusting the value appropriately in the OnSave event). If the new value is greater then check that the difference is less than the in stock value.

Also, since the test for allowing the qty value is being done at the field level and the update of the stock level is being done at the record level there could be a time lag in when this happens. It is possible for a second user to be processing the same product. Both could enter a qty and it would pass the test because their records have not yet updated the stock level. So it may be prudent to re-test the stock level in the update when the record is saved. And the event used would then need to be the canSave event.

[CALocklin]

Re: Need Script

A note on the code you posted. I think when you edited you left the line tc = table.current() in the wrong spot.

Yep. The tc = table.current() should have been the second line. I certainly needs to come before the IF statement!

Also, since the test for allowing the qty value is being done at the field level and the update of the stock level is being done at the record level there could be a time lag in when this happens. It is possible for a second user to be processing the same product. Both could enter a qty and it would pass the test because their records have not yet updated the stock level. So it may be prudent to re-test the stock level in the update when the record is saved. And the event used would then need to be the canSave event.

Good point! I hadn't thought of that issue. It just keeps gettin' easier and easier.

EDIT:

I just thought of something else. When you delete the record, the quantity doesn't change. So, if you want to be able to delete records, you may need to include something that checks the a_deleting_record variable (a system variable) and take appropriate action.

[[email protected]]

Re: Need Script

Almost there thanks to all. Thought I would handle the records being cerified first :

On the Canwritefield under field Events this is my code:

'Date Created: 16-Feb-2009 07:28:55 PM
'Last Updated: 16-Feb-2009 07:28:55 PM
'Created By : Ryland
'Updated By : Ryland
DIM SHARED orig_qty as N
tc = table.current()
orig_qty = tc.quantity
IF tc.quantity = orig_qty
END if
tp = table.open( "Product" )
tp.index_primary_put("product_id")
tp.fetch_find("tc.product_id")
IF tc.quantity > tp.qty_auth

msgbox("quantity Issued Cannot Exceed Authorized Quantity Please Re-enter")


END IF
tp.close()
End
__________________________________________________

On the Records Events in the OnsaveRecord:

I'm getting into the fields, but the Product table is not being updated. Am I missing a on commit ??? Starting just using hard code values but the record does not change.

'Date Created: 16-Feb-2009 07:48:18 PM
'Last Updated: 16-Feb-2009 07:48:18 PM
'Created By : Ryland
'Updated By : Ryland
DIM SHARED orig_qty as N
tc = table.current()
orig_qty = tc.quantity

tp = table.open( "Product" )
tp.index_primary_put("product_id")
tp.fetch_find("product_id")

msgbox("in OnSaveRecord")
tp.change_begin()
tp.qty_in_sto = 100 'tp.qty_in_sto - (tc.quantity-orig_qty)
tp.usage = 200 ' tp.usage + tc.quantity
tp.change_end()

tp.close()
End

Figured if I get these two resolved, then the rest will come...

Thanks,

Ryland