Filters and tGrid Field Display (Page 1 of 2)

Hi Derek,

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

My thinking was linking refunds table to invoice table would do the job. In any case I don't think I could have come up with the combo usage.

I guess you wanted me to apply same to sales for me to learn bit more; not because you couldn't do it. I have applied your solution for purchase part to original sample project for learning purposes. However, failed to apply same method to sales.

Also, I couldn't get refund prices displayed with minus (-)

Hi Adam,
I've added the bit that fills in the missing SIR information and it seems to work.
With regard to showing refund prices with minus(-), I don't believe it would ever work using your approach;  it looks like you perform a (0-value) instruction every time the refund record is edited - therefore every time you edit it, the value flips between positive and negative. Pretty confusing!
The way I'd do it is to simply put a message up to tell the user they have to enter a negative value;  trying to automate it seems overly complicated, at least within your current script.
In my opinion (for what it's worth - LOL!), I think there are things wrong, both from the data structure and from the coding.  But maybe this isn't a proper project and you're just trying various things out in which case, no problem.
Derek.

Hi Adam,
Two things stood out when looking at the 'tgrid field display' project you attached in your latest post:
1.  I'm not sure why you need a separate 'refunds' table - isn't a refund just another transaction type (you already have purchase and sales transaction types on your 'invoices' table). 
Also, as the structure of the 2 tables is basically the same.  I think storing your different transaction types in 1 table would simplify things a lot;  I would probably rename 'invoice' to 'transaction' and create a related 'transactiontype' table (with fields 'type' and 'dbcr') and see if that approach streamlined things.
2.  I noticed that in your script, if you create more than 1 refund transaction against the same purchase or sales invoice, this actually creates a duplicate purchase or sales invoice record;  maybe you are already know this.
I am aware that you are probably just 'trying things out' at the moment so maybe that is why you are doing some things in a certain way.  At the end of the day, we all take different approaches;  hopefully, this might give you a couple of alternatives.
Derek.

Adam, I had a few minutes to take a look at your project updated by Derek. Sorry, I have not had much time to get on the forum these days. The reason you are getting duplicates in the main invoice grids is as follows: The refunds table is a child of the invoice table. Since you are displaying refund fields (Specifically refund number in this case) in the main invoice grids, the tablegrids is doing what it is suppose to do by showing all the child refund data as well as the main invoice data in the grid. If you remove the PIRnum and SIRnum from the respective main invoice grids, then the duplicates will go away.

EHW,

I hope you are around and have some free times...

I got the two tables version working the way I wanted thanks to your and Derek's help. Hopefully I won't have any further issues with it.

Now I'm trying one table version.
Main stumbling block for me here SQL script in order to save mostly same data as a different record (refunds). I Googled for sql script but couldn't find anything that I could learn and use.
Perhaps you could give me a quick info about items highlighted on the illustration below:

On attached one table sample project I couldn't get dbRef and crRef fields under PI and SI Ref columns. I may be wrong but I'm guessing I couldn't get sql script right. I like to use these columns for conditional codes such as row coloring, preventing delete action if item contains any refund etc.

They work OK on two tables version as you can see on the screen cap below:

Also when refunds relating to Invoice record is/are deleted, Inv tGrid updates by changing color to original record color and takes out Db / Cb text under Ref column.

Thank you very much for the info and readable SQL script format EHW......................

Indeed, readable SQL script format you kindly put up makes better sense for me now.
I have updated SQL script with ref fields and it works find without errors. I noticed insert into... field order and values field order needs to be same.

SqlExecute('Insert into Invoice (Counter1,Counter2,Counter3,Counter4,DBCRvalue,product,crRef,dbRef,PIprice,PIRprice,'
    + 'SIprice,SIRprice) Values ('
       + '"' + frmRefund.EdCounter1.Text   + '",'
       + '"' + frmRefund.edCounter2.Text   + '",'
       + '"' + frmRefund.edCounter3.Text   + '",'
       + '"' + frmRefund.edCounter4.Text   + '",'
       + '"' + frmRefund.edDBCR.Text       + '",'
       + '"' + frmRefund.edInvProduct.Text + '",'
       + '"' + frmRefund.edCRref.Text      + '",'
       + '"' + frmRefund.edDBref.Text      + '",'
       + frmRefund.edPIRpricePI.sqlValue   + ','
       + frmRefund.edPIRprice.sqlValue     + ','
       + frmRefund.edSIRpriceSI.sqlValue   + ','
       + frmRefund.edSIRprice.sqlValue     + ')' );

Also what would be the field type syntax for image fields under values? I tried DBimage but didn't work.

My guess about  crRef and drRef not working seems wrong. Refunds SQL script is working OK but  crRef and drRef  are still not shown on their respective tGrids in spite of  DBupdate lines included?

Hi Derek,

Thank you so much for taking time to for doing 1 table version................
Truly appreciated...........

Your approach deserves a laud applause. You did all that with minimal script.

On downside, at my end due to luck of proper understanding of MVD functionality, changing something breaks it. For instance I changed PI Grid "Show Refunds" button name and lost button functionality (clicking on a record on PI grid to display purchase refunds of selected purchase inv record) I could only find that button name referenced on script once. I changed that too but still couldn't make it work? I'm sure I'm missing something but in spite of trying and checking all the items I can think of, couldn't solve it.

Thanks a lot Derek.................

I didn't notice edit1 was associated to the button with incremental search. It works fine now.
I noticed you have used two different filters on PR and SR tGrids. I guess you done them this way for teaching purposes as both works OK with same type of filters when both contains tparent field such as:
types.identifier="PR"
types.identifier="SR"

Edit: 16/07/2017 11:29:34
I have noticed one issue. In order to avoid orphan refund records it needs protection (foreign key restrain type) against deleting a SI and PI record with refunds with a message dialog something like "This record contains refund and cannot be delete. Refund record/s must be deleted first"
--
I have tried same approach as in sample project at post #11 by adding a text field (refund) to transactions table and put it with default value R on PR and SR forms then used them on PI and SI tGrids. However, I hit the same issue of getting refund field displayed on SI and PI tGrids.

I'd love to see from you sample project forcing limits of MVD, showing us all sorts of hat tricks with minimal script one day....

Looks like EHW is busy these days.
Derek, could you have a look at the issue with sample project at my post #11 please, when you have bit of a time?

Hi Derek,

Thank you very much for your kind update..........
Truly appreciated....................

No, it's not your bad. Actually it's your good. It shows alternative ways doing same thing. I like alternatives. After all we all need Plan A, Plan B etc to complete tasks.

One thing still bugging me which I couldn't get it working. I wanted to add reference column (*) to form1 PI and SI tGrids via PR and SR forms. I used a text field with default value R
I like to use reference columns for conditional manipulation of those relevant tGrids, in this case PI and SI tGrids. Could you please help?

Hi Derek,

Thank you very much for your kind update...........

There seems to be a strange update issue.
After deleting some refunds it correctly clears PI and SI reference column value. However, after re-run reference column value appears while there is no refund associated to particular invoice. Strange part is sometimes you need to run project multiple times to see the issue.Please see the screen cap below:

I have tried DBupdate for both save and delete buttons (onAfterClick) but no luck...

-----------------------
Edit: 18/07/2017 03:15:20
I have used the following on tGrids PR and SR onChange event. It seems to be working OK, but not sure it's correct tho?

if form1.tgMainPIR.rowcount = 0 then Form1.tgMainPI.Columns[1].Clear;

Edit2: Nope, the above doesn't seems to be working after multiple runs.

Yes, with reference column we can manipulate the tGrid and it's items in certain degree.

Hi Derek,

Unfortunately, same issue remains.
I have deleted few refunds from PR tGrid, exit and re-run it from MVD and got extra SR value under * column on SR tGrid.

I may be totally wrong but I suspect issue originates from dbUpdate/refresh as it happens on start.