1 Topic by eyeman303 (edited by eyeman303 2022-10-02 12:11:24)

  • Registered: 2018-12-02
  • Posts: 151

Topic: Calculated field: Problem with CASE expression

Hello,
I want to create a calculated field ( cf_discount) in the table aa_005_Spectacle_Order whose value will be lesser of the Frame_price and Lens_Price in the order. The spectacle-buyer needs to pay only for the Frame or the Lens, whichever is greater, and the value of the other item equals the discount. Something is going wrong. Please help.
Thanks,
Eyeman

Post's attachments

Attachment icon Clinic_Discount.zip 496.62 kb, 135 downloads since 2022-10-02 

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Calculated field: Problem with CASE expression

Hi Eyeman,
Please have a look at the attachment;  the discount calculation should now be working as you want.
There were a couple of other things I noticed.
1.  You don't need calculated fields for the lens price or the frame price - you get that simply because you already have a relationship between those tables and the spectacles order table.
2.  I encountered a problem when editing records.  I think this is because you were using tab sheets on Form F5_Spectacle_Order and specifying tables LU_005 and LU_006 rather than AA_005 so the program didn't know how to retrieve existing order details;  I've changed it to use comboboxes as the easiest way to get it working.
I also made F1_Patients_record tablegrid wider but only so it was easier to check what was going on so just change it back to how you want it.
Regards,
Derek.

Post's attachments

Attachment icon Clinic_Discount fixed.zip 497.39 kb, 127 downloads since 2022-10-02 

3 Reply by eyeman303 (edited by eyeman303 2022-10-02 16:41:49)

  • Registered: 2018-12-02
  • Posts: 151

Re: Calculated field: Problem with CASE expression

Thank you so much Derek. The solution works perfectly. The calculated fields for Lens price and Frame price are definitely not required. I was trying to get the CASE expression right.
In F5_Spectacle_Order, I have used 2 tablegrids for Frames and Lenses. As there are 100s of frames and lenses, I thought of adding few Search criteria (eg price range)  in each of the tabsheet. In this situation, where will the search function work better- tablegrid or combobox? Also as you found editing a record becomes little problematic with Tablegrid, which will be the better option?
Regards,
Eyeman

  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,673

Re: Calculated field: Problem with CASE expression

Hi,
If there are many frames and lenses to chose from and you want to do something like search by price range, then the case for using tablegrids with search criteria becomes much stronger.
But I don't think I'd use tab sheets - it seems more user friendly to have the frames tablegrid side by side with the lenses tablegrid and just select from each.
Try it something like the attached example.
Derek.

Post's attachments

Attachment icon Clinic_Discount fixed2.zip 498.83 kb, 147 downloads since 2022-10-02 

5 Reply by eyeman303 (edited by eyeman303 2022-10-03 06:35:29)

  • Registered: 2018-12-02
  • Posts: 151

Re: Calculated field: Problem with CASE expression

Hello Derek,
Thanks again. A new learning for me, how to combine the best of tablegrid and combobox properties. Now detailed search operations can be done on the tablegrids and the selected record gets updated in the comboboxes, which are saved. I agree, keeping 2 tablegrids side-by-side make it  easier for data-entry than using page-control. Also I noticed that in the CASE expression, you have used >= sign instead of > sign. This addresses the situation when the lens_price equals the frame_price.
Regards,
Eyeman