New Calculated and Variable Fields in Set Design Mode

[goldberg2727]

With the latest update to alpha 5 v 10.5. There is a new layout for the Set Design View that includes two tabs for calculated and variable fields. I was curious to know how these fields work. Can I use the new variable fields in the set in the following way?...

I have a set with a Parent, Child, Grand Child, and a great grand child. tables linked by are single ID field in the parent table.

tblProjects
|
|===>tblWorkOrder(1:M0
|
|
|=====>tblworksheets
|
|=====tblworksheet_items
|=====>tblworsheet_trucks
|=====>tblworksheet_employees

When I an record to the item, trruck, or employee table I need to determine which bill to use for the item, employee, or truck record. This isn't a simple lookup because the rate is determined by the customer the work is being done for as well as several other factors. For example the rates table for the items has the following fields....

Rec_no
Cust_Id
Item_Code
Itm_desc
Itm_Units
Bill_Rate1
Bill_Rate2
Bill_Rate3
Pay_Rate1
Pay_Rate2
Sub_Rate1
Sub_Rate2
Sub_Rate3
Sub_Rate4

Which bill rate and pay rate to use is determine by a value in the tblworksheet table(tblworksheet->bill_Rate_id and tblworksheet->pay_rate_id. Also stored in the WorkOrder table is a field that indicated whether to the rates should be multiplied by 1.5 for overtime. So I created a UDF field to look up the value of bill and pay rates fields in the tblworksheet_items table...

item_rates(tblproject->Cust_ID,tblworkorder->OT,tblworksheet->Bill_Rate_ID,tblworksheet_Items->item_code)

Since most of these values come from different tables I was using lookup() functions to determine the values of the parameters for the my UDF (Item_Rates()). I soon realized that this was too much calculating and was way too slow for efficient data entry.

So I then created fields in the tblworksheet_items table for each parameter in my item_rates udf that was currently retrieved by a lookup() function. Each of these fields had a field rule with a "Simple Initial Value" which contained a calculation for retrieving the value needed in my udf. So now my tblworksheet_itme->bill rate field, I had a calculated value using my UDF that looked like this

Items_Rates(Cust_id,Overtime, bill_rate_id,Item_Code)

I also had written code for the tblproject->cust_id, tblworkorder->overtime, and tblworksheet->bill_rate_id,field rules that were triggered by the events->CanWrite. The code would audit the changed value and pass that value to the great geand child table linked to the record being changed and then recalculate the bill rate for those records. (ie if the customer on the project is changed after workorders, worksheets, and worsheet_items were added to the that project, then the rates for that new customer would be applied to the current worksheet_items.

Now I am wondering that instead of having to do lookup in the worksheet_items table for these values in different tables in the my set, can I simple have a function in a calculated field that uses values stored in the "set variable tab" so my new Itesm_rate() UDF in the tblworksheet_items table would be

item_rates(var->Cust_ID,var->Overtime,var->Bill_rate_id,var->item_code)

If this would not work, what would you all suggest the best way to go about calculating these rates would be???

[Peter.Greulich]

Re: New Calculated and Variable Fields in Set Design Mode

There is a new layout for the Set Design View that includes two tabs for calculated and variable fields

FYI: These have existed in ALL versions of A5.