26 Reply by derek (edited by derek 2017-08-24 14:24:36)

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

Re: Refunds again

Hi Adam, Hi EHW,
After you mentioned in a previous post that everything that comes in on one purchase invoice goes out on one sales invoice (so a strict 1:1) but that there can be a number of adjustments (refunds) against the original purchase invoice, it seemed to me (unless I'm missing something) that from a data design point of view, the simplest thing would have been to hold both purchase and sales information on one single record.  Then you don't have to worry about creating additional records, maintaining links between related sales and purchases, leaving orphaned records, losing traceability  etc etc. 
Simplest is often the best if the design doesn't compromise functionality and I don't believe it does in this case.
Looking back over the history of this topic,  the options have gone from having separate tables for purchase and sales invoices, using the same table but with discrete purchase and sales records and now it seems you can use not only the same table but also the same record!
I haven't attempted to replicate all of the functionality of your existing project but I'm pretty sure that it's possible using the above suggested schema. 
I've knocked up a quick demo to prove some of the more basic functionality - the whole thing can actually work without any script at all (always my target!):  the few lines of script that I've used are only cosmetic, to make it a bit more user friendly.
I appreciate that you and EHW have done a lot of work on this so it's possibly too far down the road to want to change now, but it might yet prove to be of some use.
Regards,
Derek.

Post's attachments

Attachment icon adam simplified refunds.zip 349.42 kb, 378 downloads since 2017-08-24 

  • Registered: 2016-07-28
  • Posts: 1,030

Re: Refunds again

Hi EHW,


Thank you very much.................
Truly appreciated.......................


I have some additional questions but you can ignore them if you wish as you have already helped me a great deal on this matter.


1. Under sale inv onShow

frmInvoice.cbSIcustCountry.dbItemID := -1;
...

what the above line does?


2. How can I change each text line font style, size and color in supplier memo field?
For instance, making Supplier name line font size 10 and style underlined or bold.


3. Is it possible to hide lines or copied fields within supplier memo field when there is no data entered for certain fields?. For instance if there is no city, lines moves up to fill blank (visially) space (I moved city to it's own line rather than having city, state and zip on same line) or if there is no state, can we move zip code to a left (filling the spacer)


4. This would be a bonus if you also provide getting supplier fields into supplier memo field using SQLExecute. What you have done perfectly fine for this project. It'd be great reference info for my code repository for future projects.

Adam
God... please help me become the person my dog thinks I am.
  • derek
  • derek
  • Moderator
  • Offline
  • Registered: 2015-02-16
  • Posts: 2,676

Re: Refunds again

Hi Adam,
Not sure if something like this helps on a couple of the points you raised.  As always, probably other(better) ways to do it.
Regards,
Derek

Post's attachments

Attachment icon memolinefeeds.zip 336.68 kb, 353 downloads since 2017-08-24 

  • Registered: 2016-07-28
  • Posts: 1,030

Re: Refunds again

Hi Derek,


Thank you very much for the simple approach to refunds demo. It doesn't fit what I'm trying to do but it's a great demonstration of simple alternative approach with minimal code.
Couple of points I noticed if I'm not missing anything.
- How can we tell that refund/s belongs to purchase or sale?
- On click of Form1 TableGrid1 header produces a "List index out of bounds (-1)" error message.


Not sure if something like this helps on a couple of the points you raised.  As always, probably other(better) ways to do it.

Thanks for the example. I'll try to apply it and see if I can get it working.

Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2015-11-13
  • Posts: 652

Re: Refunds again

See attached.


frmInvoice.cbSIcustCountry.dbItemID := -1;

what the above line does?

This clears the dbitemid. For a new sale it was not cleared and showing a country for an empty customer.


2. How can I change each text line font style, size and color in supplier memo field?
For instance, making Supplier name line font size 10 and style underlined or bold.

Unfortunately you cannot change the font attributes on individual lines inside a memo field. What I did in the attached project is removed the memo field and created a panel with supplier labels inside it. Visually it looks the same with the exception that you can control fonts, colors, etc on the labels.


3. Is it possible to hide lines or copied fields within supplier memo field when there is no data entered for certain fields?. For instance if there is no city, lines moves up to fill blank (visially) space (I moved city to it's own line rather than having city, state and zip on same line) or if there is no state, can we move zip code to a left (filling the spacer)

I took care of this through script.


4. This would be a bonus if you also provide getting supplier fields into supplier memo field using SQLExecute. What you have done perfectly fine for this project. It'd be great reference info for my code repository for future projects.

I also took care of this. However, I know you are trying to learn various techniques and ways to do things, but you should try to stay away from doing extraneous SqlExecutes to retrieve data when MVD already has retrieved the data through normal processes.

Post's attachments

Attachment icon tGrid Field Display3 TwoTablesTest3.zip 617.22 kb, 392 downloads since 2017-08-24 

  • Registered: 2015-11-13
  • Posts: 652

Re: Refunds again

Nice one Derek on the memo line feeds.

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

Re: Refunds again

Hi EHW,
Thanks.  As always, I was trying for the zero-script option but had to admit defeat in the end - LOL!
Derek.

  • Registered: 2016-07-28
  • Posts: 1,030

Re: Refunds again

Hi EHW,


Great stuff... I like the idea of using labels for supplier details instead of field/s.
Thank you very much..... Truly appreciated...............


I agree, in this project, using supplier fields on invoice form since they are already there instead of retrieving them again using SQLExecute as you have suggested. Thank you for SQLExecute script which will be useful for me in future projects.


Without SQLExecute with labels I have tried the following which seems to be working OK

frmInvoice.lblSuppName.Caption := frmInvoice.cbPIsuppName.Text;
  frmInvoice.lblSuppStreet.Caption := frmInvoice.edPIsuppStreet.Text;
  frmInvoice.lblSuppCity.Caption := frmInvoice.edPIsuppCity.Text;
  frmInvoice.lblSuppState.Caption := frmInvoice.edPIsuppState.Text;
  frmInvoice.lblSuppZip.Caption := frmInvoice.edPIsuppZip.Text;
  frmInvoice.lblSuppCountry.Caption := frmInvoice.cbPIsuppCountry.Text;

  If frmInvoice.lblSuppZip.Caption = '' then
    Begin
      frmInvoice.lblSuppZip.Caption := frmInvoice.lblSuppCountry.Caption;
      frmInvoice.lblSuppCountry.Caption := '';
    End;

  If frmInvoice.lblSuppState.Caption = '' then
     Begin
       frmInvoice.lblSuppState.Caption := frmInvoice.lblSuppZip.Caption;
       frmInvoice.lblSuppZip.Caption := frmInvoice.lblSuppCountry.Caption;
       frmInvoice.lblSuppCountry.Caption := '';
     End;

  If frmInvoice.lblSuppCity.Caption = '' then
    Begin
      frmInvoice.lblSuppCity.Caption := frmInvoice.lblSuppState.Caption;
      frmInvoice.lblSuppState.Caption := frmInvoice.lblSuppZip.Caption;
      frmInvoice.lblSuppZip.Caption := frmInvoice.lblSuppCountry.Caption;
      frmInvoice.lblSuppCountry.Caption := '';
    End;

  If frmInvoice.lblSuppStreet.Caption = '' then
    Begin
      frmInvoice.lblSuppStreet.Caption := frmInvoice.lblSuppCity.Caption;
      frmInvoice.lblSuppCity.Caption := frmInvoice.lblSuppState.Caption;
      frmInvoice.lblSuppState.Caption := frmInvoice.lblSuppZip.Caption;
      frmInvoice.lblSuppZip.Caption := frmInvoice.lblSuppCountry.Caption;
      frmInvoice.lblSuppCountry.Caption := '';
    End;
Adam
God... please help me become the person my dog thinks I am.
  • Registered: 2015-11-13
  • Posts: 652

Re: Refunds again

Hey if it works that's all that matters. I think I would have used the variables though. It's a whole lot less typing. But that's just a personal preference.


Street := frmInvoice.edPIsuppStreet.Text;
  City := frmInvoice.edPIsuppCity.Text;
  State := frmInvoice.edPIsuppState.Text;
  zip := frmInvoice.edPIsuppZip.Text;
  StateZip := State + ' ' + Zip;
  Country := frmInvoice.cbPIsuppCountry.Text;
  If State = '' then StateZip := Zip;
  If StateZip = '' then
    Begin
      StateZip := Country;
      Country := '';
    End;
  If City = '' then
    Begin
      City := StateZip;
      StateZip := Country;
      Country := '';
    End;
  If Street = '' then
    Begin
      Street := City;
      City := StateZip;
      StateZip := Country;
      Country := '';
    End;

  frmInvoice.lblSuppName.Caption := frmInvoice.cbPIsuppName.Text;
  frmInvoice.lblSuppAddr1.Caption := Street;
  frmInvoice.lblSuppAddr2.Caption := City;;
  frmInvoice.lblSuppAddr3.Caption := StateZip;
  frmInvoice.lblSuppAddr4.Caption := Country;
  • Registered: 2016-07-28
  • Posts: 1,030

Re: Refunds again

... I think I would have used the variables though. It's a whole lot less typing. But that's just a personal preference.


Indeed, it's a better way.
Thank you very much...........
Truly appreciated..................

Adam
God... please help me become the person my dog thinks I am.