conditional posting - is it possible ?

[vasant panjabi]

Table: A � field: inv_no

Table: B � 5 fields � inv1 inv2 inv3 inv4 inv5

Is it possible to post the value of inv_no to inv1 if the value of the inv1 field is blank else post it to inv2 or post it to inv3 if inv1 & inv2 already have a value & so on.

Value of inv_no is different for each field in table: B and it should be posted in the 1st available blank field only ONCE without touching the other fields.

regarding the index link - that is no problem & can manage that part of the operation.

Any help appreciated.

Rgds,
v.p

[Stan Mathews]

RE: conditional posting - is it possible ?

Never tried this but it should work.

Design a saved post operation to post the inv_no to inv1. Turn on the script recorder (low level statements) and run the operation. Capture the script, edit it to name the proper tables, edit the statement which puts the value in the inv1 field, where inv1 is named as the target, substituting something like:

case(isblank("inv1"),inv1,isblank("inv2"),inv2,isblank("inv3"),inv3,isblank("inv4"),inv4,isblank("inv5"),inv5)

only the first true condition of the case will be executed.

[Tom Cone Jr]

RE: conditional posting - is it possible ?

v.p.

Whenever I see a table structure like Table: B in your example, that contains a series of repeating fields, all containing the same thing... an invoice number, I wonder whether the design of the data set might be improved. Often this can be achieved by putting the repeating fields in a separate table that only has two fields, a link field, and the repeating field.

This will greatly simplify many set designs. In your present arrangement if you try to link table A and table B using the invoice number the link must include any of the possible Table: B fields. If the repeating fields were in another table and each table in that record held only a single invoice number, the link would be much more efficient.

As a general rule (recognizing that there may be exceptions from time to time) I try hard not to overload a field by including multiple values in a single field. Your design of Table: B seems similar. The record structure in Table: B seems overloaded, since multiple values (of invoice number) are being stored in a single record.

-- tom

[vasant panjabi]

RE: conditional posting - is it possible ?

dear tom,
table:B is our orders table & working fine by itself.
when orders are shipped out we enter in in dlv1 qty1 dlv_dt1.
hwr, in case shipped partially then in delv1 & delv2 &
so on.

table A: is the invoice table.. & all we trying to do is to assure that all orders shipped out are invoiced by posting the invoice no. in the inv1 field.

please note an order may be shipped weeks apart & invoice no & invoice date are different. . hwr, generally orders are shipped 90% of the time in one lot - in this case posting is simple but few cases 2 or 4 items.

the purpose is that if the invoice no. is posted in the orders.dbf then it has been invoiced.

the common link between the 2 tables is the combination of orderno+product_id - example: ord456/prod123

our general rule is - one record one product per order - while several invoices can exists for that order.
problem is that cannot make a set as the invoice can contain
items of other orders & other products (total 8 items can be invoiced in one invoice if the buyer is same). order/product nos. are entered with every item in the invoice.

any ideas.

rgds/v.p

[Tom Cone Jr]

RE: conditional posting - is it possible ?

v.p.

I'm glad it's working for you.

I also would not do it the way you're doing it, but to each his own. To me it would be more logical (efficient?) to store the information on an order in the parent table, and link that table to one or more delivery records, each of which would receive a unique billing or invoice number. In such a design if an order takes 5, 6, or 20 shipments to complete there's no problem even if it's spread over many weeks. In your case, if you run out of invoice and delivery date fields in your child table you're stuck.

-- tom

[Stan Mathews]

RE: conditional posting - is it possible ?

First - ! � I agree with Tom�s comments, completely.

Second - if you want to proceed with your original design, I wanted to see if the post could be designed.


The following script will, I believe, demonstrate that you can do what you want. It accomplishes this by actually performing five posts, it just replaces the value in the field with �itself� if the field is not blank originally. Thus the post is performed if all �prior� fields are filled and the current field is not.


If you are sure your data is consistent, then you could eliminate some of the multiple tests (isnotblank().and.isnotblank().and�) because it should never occur that an earlier field would be blank while a later field is not.

a_tbl = table.open("sku")
post.t_db = "avail_inv2.DBF"
post.m_key = "ITM_CD"
post.t_key = "ITM_CD"
post.m_filter = ""
post.t_filter = ""
post.m_count = 5
post.m_field1 = "Oh5"
post.m_exp1 = "if(isnotblank(\"oh1\").and.isnotblank(\"oh2\").and.isnotblank(\"oh3\").and.isnotblank(\"oh4\").and.isblank(\"oh5\"),@Avail_Inv2->_001,Oh5)"
post.m_field2 = "Oh4"
post.m_exp2 = "if(isnotblank(\"oh1\").and.isnotblank(\"oh2\").and.isnotblank(\"oh3\").and.isblank(\"oh4\"),@Avail_Inv2->_001,Oh4)"
post.m_field3 = "Oh3"
post.m_exp3 = "if(isnotblank(\"oh1\").and.isnotblank(\"oh2\").and.isblank(\"oh3\"),@Avail_Inv2->_001,Oh3)"
post.m_field4 = "Oh2"
post.m_exp4 = "if(isnotblank(\"oh1\").and.isblank(\"oh2\"),@Avail_Inv2->_001,Oh2)"
post.m_field5 = "Oh1"
post.m_exp5 = "if(isblank(\"oh1\"),@Avail_Inv2->_001,Oh1)"
post.t_count = 0
a_tbl.post()
a_tbl.close()


As Tom indicated, what happens when all five fields are filled? This script would ignore the current inv_no, preserving current data in the master "post to" table.