Simple Sample

rgates@ttcdas.com

Can someone provide me with a simple code sample that does something like this?

The process is to run a query that returns rows from a BO (will call this Master). With each row from the Master BO I want to create rows in another BO (we will call this Secondary). Also, with each row that I create in Secondary I need to create a special key that gets assigned to one of the fields in Secondary. The KEY is based off of each change in the Master BO within the column CATEGORY. So, every time CATEGORY changes I increment the KEY by 1.

tford

Is this related to your post at:

rgates@ttcdas.com

Yes, I have been messing around with it for a little while, but still can't get it to work correctly.

tford

Go ahead and post what you have so far & we'll see if we can help you tweak it.

rgates@ttcdas.com

Thie process has the following Rules. The Key field that I am trying to make is Product_Price.CLIN_Number

rResetProcessVariables
LoggedInRegularUser.ApplicationVariable_CLIN_Index1=1

LoggedInRegularUser.ApplicationVariable_CLIN_Index2=0

LoggedInRegularUser.ApplicationVariable_CLIN_Index3=0

LoggedInRegularUser.ApplicationVariable_CLIN_Title=''

rPickSchedule
PICK FROM 'qPROCESS - pCreateProductPriceWithCLINNumbersAssigned PRICESCHEDULE'

rFindCurrentProducts
FIND 'qPROCESS - pCreateProductPriceWithCLINNumbersAssigned PRODUCT'

rCreateProductPrice
CREATE Product_Price FOR EACH Product WITH Product_Price.Price=ThisPrice_Schedule.Rate*Product.List_Price,Product_Price.Price_Schedule_ID=ThisPrice_Schedule.ID,Product_Price.Title=Price_Schedule.Title,Product_Price.X_Product=Product,Product_Price.CLIN_Description=Product.X_Ref_Product_Family.CLIN_Title

*** Note I broke the creating of the Product_Price as a first step. I wanted to assign the CLIN_Number as part of the creation of the Product_Price, but couldn't figure out how to have IF statements and the logic below embedded with in the rCreateProductPrice

rFindProduct_Price
FIND Product_Price WHERE Product_Price.Title=ThisPrice_Schedule.Title ORDER BY Product_Price.Product_Family ASC, Product_Price.Product_Part_Number ASC

rCheckIndexFamily
If ThisProduct_Price.X_Product.Family<>LoggedInRegularUser.ApplicationVariable_CLIN_Title Then LoggedInRegularUser.ApplicationVariable_CLIN_Title=ThisProduct_Price.X_Product.Family
LoggedInRegularUser.ApplicationVariable_CLIN_Index3=0

rIncrementIndexes
LoggedInRegularUser.ApplicationVariable_CLIN_Index2=LoggedInRegularUser.ApplicationVariable_CLIN_Index2+1
LoggedInRegularUser.ApplicationVariable_CLIN_Index3=LoggedInRegularUser.ApplicationVariable_CLIN_Index3+1

rCheckIndex
If LoggedInRegularUser.ApplicationVariable_CLIN_Index2>26 Then
LoggedInRegularUser.ApplicationVariable_CLIN_Index1=LoggedInRegularUser.ApplicationVariable_CLIN_Index1+1
LoggedInRegularUser.ApplicationVariable_CLIN_Index2=1

rSetCLINNumber
Product_Price.CLIN_Number='00'+CHARS_FROM_RIGHT(CHARS_FROM_LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',LoggedInRegularUser.ApplicationVariable_CLIN_Index1),1)+CHARS_FROM_RIGHT(CHARS_FROM_LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',LoggedInRegularUser.ApplicationVariable_CLIN_Index2),1)+CHARS_FROM_RIGHT('000'+AS_STRING(LoggedInRegularUser.ApplicationVariable_CLIN_Index3),3)

rgates@ttcdas.com

I was able to get this working by putting all of the rules starting with rCheckIndexFamily into the Product_Price BO and checking if Product_Price is NEW. By doing so it eliminated the need for the second pass of the data via the rule rFindProduct_Price not needed.

The thing that isn't working is that the data being created isn't in the proper order.

My Process now looks like this:
rResetProcessVariables
LoggedInRegularUser.ApplicationVariable_CLIN_Index1=0
LoggedInRegularUser.ApplicationVariable_CLIN_Index2=1

LoggedInRegularUser.ApplicationVariable_CLIN_Index3=0

LoggedInRegularUser.ApplicationVariable_CLIN_Title=''

rPickSchedule
PICK FROM 'qPROCESS - pCreateProductPriceWithCLINNumbersAssigned PRICESCHEDULE'

rFindCurrentProducts
FIND Product WHERE Product.Revision_Current_YN='Yes' ORDER BY Product.Part_Number ASC

Here is the issue:

If I sort the Product_Price values after they get created, I can tell that the process rule rFindCurrentProducts is for some reason ignoring the ORDER BY Product.Part_Number sorting. The other possibility is that the rCreateProductPrice for some reason ignores the sort order specified in rFIndCurrentProducts. I have even tried using a query in the rFindCurrentProducts instead of the "hard coded" query.

Any ideas?

rgates@ttcdas.com

I have confirmed my findings by creating a test BSV. The test BSV has 2 BO. File1 and File2. Each have only 1 field, each called Field1.

I have a TestProcess that just has these 2 lines.

FIND File1 WHERE File1.Field1<>'' ORDER BY File1.Field1
CREATE File2 FOR EACH File1 WITH File2.Field1=File1.Field1

I start the test bsv and randomly enter in the values into File1. Once complete I run the TestProcess.

Take a look at the results. The data is inserted into File2 randomly versus what I would of thought would have been sorted by File1.Field1.

Am I wrong to assume that the records would of gotten created in File2 in order of the FIND command in the process?

The reason this is so important is because I am trying to sort data to assign a special key to the data.

tford

Try splitting this into 2 processes.
First process will have FIND and call the 2nd processs.
2nd process will have the CREATE.

rgates@ttcdas.com

Nothing changed. I got the same results.

tford

Looks like a small bug to me. I will send a sample BSV to support.

This rule works (it excludes the WHERE):
FIND File1 ORDER BY File1.Field1 IN BATCHES OF 1
CREATE File2 WITH File2.Field1=File1.Field1

This rule does not comply to the ORDER:
FIND File1 WHERE File1.Field1<>'' ORDER BY File1.Field1 IN BATCHES OF 1
CREATE File2 WITH File2.Field1=File1.Field1

aware_support

In the following rule

FIND QueryName ORDER BY ...

the ORDER BY condition is ignored and the sorting order is taken from the QueryName query.

In the following rule

FIND QueryName WHERE ... ORDER BY ...

the ORDER BY condition is not ignored (correctly).

This is a small bug. As a workaround use the following rule:

FIND QueryName WHERE 1=1 ORDER BY ...

rgates@ttcdas.com

I am a bit confused as to what I should be doing. Should I be using a query instead of sorting at the FIND command level?

rgates@ttcdas.com

The WHERE clause is not permitted within a Rule where the dataset is based off of a query.

aware_support

Note that File1 is also the name of the query, so I am assuming you need to use the query name in the FIND action, not the object name. It is rather confusing and I suggest you rename the query to something else.

If you want to sort in the chronological order do this:
FIND File1 WHERE 1=1 ORDER BY File1.ID IN BATCHES OF 1
CREATE File2 WITH File2.Field1=File1.Field1

FIND File1 WHERE File1.Field1<>'' ORDER BY File1.ID IN BATCHES OF 1
CREATE File2 WITH File2.Field1=File1.Field1

If you want to sort by Field1 use it in the ORDER BY instead of ID

rgates@ttcdas.com

I'm still having a problem. Using a query name in the FIND statement with a WHERE clause gives me an error:

Syntax error at token "WHERE" (column 70).

I am able to user the WHERE clause only when I reference a BO versus referencing a Query.

bartthefish

I'm still having a problem. Using a query name in the FIND statement with a WHERE clause gives me an error:

Syntax error at token "WHERE" (column 70).

You cannot specify additional WHERE Clauses to a query than those that it is created with.

When determining whether to use a Query or the BO Name in the FIND statement; I would say that it depends on what you want to do with the results. eg:

If you want to display the results to the user so that the user can specify which records to process etc then use a Query. (This enables you to specify the attributes the user will see etc. But you cannot use WHERE in the FIND statement, you have to specify the WHERE clauses in the query itself)

If you want to work with records in the background and don't require user interaction with the processing it is often better to use the BO name. (And the WHERE will not give you a Syntax error)

It appears to me from your post that what you want to do does not require user interaction at all.

Better to go with the
FIND File1 (BO) WHERE File1.Field1<>'' ORDER BY File1.ID IN BATCHES OF 1
CREATE File2 WITH File2.Field1=File1.Field1

rgates@ttcdas.com

FIND Product WHERE Product.Revision_Current_YN='Yes' ORDER BY Product.Family, Product.Part_Number IN BATCHES OF 1

CREATE Product_Price WITH Product_Price.Price=ThisPrice_Schedule.Rate*Product.List_Price,Product_Price.Price_Schedule_ID=ThisPrice_Schedule.ID,Product_Price.Title=Price_Schedule.Title,Product_Price.X_Product=Product,Product_Price.CLIN_Description=Product.X_Ref_Product_Family.CLIN_Title

Using the above FIND statement does now provide the right sequence, but it takes a 7 minutes to process 1700 records versus 1 minute (without the IN BATCHES OF 1 clause). Secondly, I have rules that are at the BO Product_Price that only work on the first Product_Price record created versus each record created. If I remove the IN BATCHES OF 1 clause the rules at the Product_Price execute, but are not assigned properly because the records are out of order. All of these rules are in place to create/assign the Product_Price.CLIN_Number.

Here are the rules:
RULE1:
If Product_Price.X_Product.Family<>LoggedInRegularUser.ApplicationVariable_CLIN_Title AND Product_Price IS NEW AND LoggedInRegularUser.ApplicationVariable_CLIN_UpdateYN='Yes' Then
LoggedInRegularUser.ApplicationVariable_CLIN_Title=ThisProduct_Price.X_Product.Family
LoggedInRegularUser.ApplicationVariable_CLIN_Index1=LoggedInRegularUser.ApplicationVariable_CLIN_Index1+1
LoggedInRegularUser.ApplicationVariable_CLIN_Index2=1
LoggedInRegularUser.ApplicationVariable_CLIN_Index3=0

RULE2:
If Product_Price IS NEW AND LoggedInRegularUser.ApplicationVariable_CLIN_UpdateYN='Yes' Then
LoggedInRegularUser.ApplicationVariable_CLIN_Index3=LoggedInRegularUser.ApplicationVariable_CLIN_Index3+1

RULE3:
If (LoggedInRegularUser.ApplicationVariable_CLIN_Index2=12 OR LoggedInRegularUser.ApplicationVariable_CLIN_Index2=15) AND Product_Price IS NEW AND LoggedInRegularUser.ApplicationVariable_CLIN_UpdateYN='Yes' Then
LoggedInRegularUser.ApplicationVariable_CLIN_Index2=LoggedInRegularUser.ApplicationVariable_CLIN_Index2+1

RULE4:
If (LoggedInRegularUser.ApplicationVariable_CLIN_Index3=12 OR LoggedInRegularUser.ApplicationVariable_CLIN_Index3=15) AND Product_Price IS NEW AND LoggedInRegularUser.ApplicationVariable_CLIN_UpdateYN='Yes' Then
LoggedInRegularUser.ApplicationVariable_CLIN_Index3=LoggedInRegularUser.ApplicationVariable_CLIN_Index2+1

RULE5:
If LoggedInRegularUser.ApplicationVariable_CLIN_Index3>26 AND Product_Price IS NEW AND LoggedInRegularUser.ApplicationVariable_CLIN_UpdateYN='Yes' Then
LoggedInRegularUser.ApplicationVariable_CLIN_Index2=LoggedInRegularUser.ApplicationVariable_CLIN_Index2+1
LoggedInRegularUser.ApplicationVariable_CLIN_Index3=1

RULE6
If Product_Price IS NEW AND LoggedInRegularUser.ApplicationVariable_CLIN_UpdateYN='Yes' Then
Product_Price.CLIN_Number=CHARS_FROM_RIGHT('0000'+AS_STRING(LoggedInRegularUser.ApplicationVariable_CLIN_Index1),4)+CHARS_FROM_RIGHT(CHARS_FROM_LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',LoggedInRegularUser.ApplicationVariable_CLIN_Index2),1)+CHARS_FROM_RIGHT(CHARS_FROM_LEFT('ABCDEFGHIJKLMNOPQRSTUVWXYZ',LoggedInRegularUser.ApplicationVariable_CLIN_Index3),1)

Also, the process that has the FIND statement in it initializes variables below:
LoggedInRegularUser.ApplicationVariable_CLIN_Index1=0
LoggedInRegularUser.ApplicationVariable_CLIN_Index2=1
LoggedInRegularUser.ApplicationVariable_CLIN_Index3=0
LoggedInRegularUser.ApplicationVariable_CLIN_Title=''
LoggedInRegularUser.ApplicationVariable_CLIN_UpdateYN='Yes'

tford

Secondly, I have rules that are at the BO Product_Price that only work on the first Product_Price record ....

Try my suggestion from Aug 12th:

Try splitting this into 2 processes.
First process will have FIND and call the 2nd processs.
2nd process will have the CREATE. [and all the other rules]

rgates@ttcdas.com

I am using a second process for the Create. I just pasted it into the post so that you could see the rule. After the FIND statement the following process is executed pCreateProductPriceWithCLINNumbersAssignedStep2 which consists command below. When I use the below command in the first process, my Product_Price.Price calculation seems to grab just the first Product.List_Price for every record created. That is why I broke it up into 2 processes:

tford

If you are using 2 processes, do not inlclude "IN BATCHES OF" in the FIND of the first process.