How do I get a record based on a data? 1

[rleiman]

Hi Everyone,

Can you tell me how to get a record in a table based on a date?

Here is the code I tried to use.

Truly,
Emad

<code>
NET:WeekEnding = '8-JUN-2006'

Get (NetWorth, NET:KeyWeekEnding)

message(NET:WeekEnding)

If LAY:CurrentBalance > LOC:LayawayCurrentBalanceBeforeChange |
Then
NET:TotalLayawayPayments = NET:TotalLayawayPayments - (LAY:CurrentBalance - LOC:LayawayCurrentBalanceBeforeChange)
End

If LAY:CurrentBalance < LOC:LayawayCurrentBalanceBeforeChange |
Then
NET:TotalLayawayPayments = NET:TotalLayawayPayments + (LOC:LayawayCurrentBalanceBeforeChange - LAY:CurrentBalance)
End

Put (NetWorth)

message(NET:TotalLayawayPayments)
</code>

 

[rleiman]

oooooops!!! my question should be "How do I get a record based on a date" not data.

Sorry.

Emad

 

[ShankarJ]

Hi Emad,

You can get a Record/Row by a KEY/INDEX only and not a Field/Column. However, if your back end database is SQL, you could define a Key in the dictionary for the Table without making any changes to your database. However if it is Topspeed you need to convert your database to the new format. An alternative is to use a VIEW with a Filter but performance will be awful if there is NO key.

Regards

 

[rleiman]

Hi ShankarJ,

The key on the NetWorth topspeed table is the field NET:WeekEnding.

In the 1st message box shown in the code, it displays just the number 8 instead of the actual date. Did I use the wrong format for the date?

By the way, is there a "day of week" function I can use to figure out if a date is on monday, wednesday, etc?

Thanks.
Emad

 

[ShankarJ]

Hi Emad,

Looks like I did not read your question properly. Dates are stored as numbers i.e. no of days since a base date. Check Clarion Standard Date/Time in the help for more info.

So ...

NET:WeekEnding = '8-JUN-2006'

should be

NET:WeekEnding = DATE(6,8,2006)

or

NET:WeekEnding = DEFORMAT('06/08/2006', @D2)

or

NET:WeekEnding = DEFORMAT('08/06/2006', @D6)

Check Date Pictures in the help for more info.

To find the day of the week, just divide the date variable by 7 and find the reminder (1 - Sunday, ...) i.e.

DayOfWeekNumber = (DateVariable % 7) + 1

DayOfWeekText = CHOOSE((DateVariable % 7) + 1, 'Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','*Error*')

Regards

 

[rleiman]

Hi ShankarJ,

Thank you for the very usefull reply.

I wish they had the ability for me to give multiple stars to you.

Truly,
Emad

 

[ShankarJ]

Hi Emad,

Thanks for the kind words.

Regards

 

[rleiman]

Hi ShankarJ,

You are very welcome.

I tried the code and it pulled up the correct record. Thanks.

Can you tell me the best ebed point to put this code? LOC:LayawayCurrentBalanceBeforeChange = LAY:CurrentBalance

And for this code as well?

NET:WeekEnding = DEFORMAT(LOC:WeekEnding, @D6)

Get (NetWorth, NET:KeyWeekEnding)

If LAY:CurrentBalance > LOC:LayawayCurrentBalanceBeforeChange |
Then
NET:TotalLayawayPayments = NET:TotalLayawayPayments - (LAY:CurrentBalance - LOC:LayawayCurrentBalanceBeforeChange)
End

If LAY:CurrentBalance < LOC:LayawayCurrentBalanceBeforeChange |
Then
NET:TotalLayawayPayments = NET:TotalLayawayPayments + (LOC:LayawayCurrentBalanceBeforeChange - LAY:CurrentBalance)
End

Put (NetWorth)

Thanks.

Truly,
Emad

 

[ShankarJ]

Hi Emad,

You did not tell which procedure you want to put the code in, so I will assume it is a FORM of the LAYAWAY table and you want to update NetWorth as a secondary update.

The code to store the local values can be in WindowManager.Init - After opening files.

The code to retrieve/update the Networth table can be in WindowManager.TakeCompleted - after parent call. Put your code within a IF NOT ReturnValue .... END as the ReturnValue will have a value if the LAYAWAY table's update was not successful.

Regards

 

[rleiman]

Hi ShankarJ,

Yes, it is for the Layaway table.

Thanks for the information. It's very helpfull.

Truly,
Emad