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

Topic: Getting data on grid from different tables and grids?

I'm trying to display products belonging to a selected supplier from supplier grid on another grid but cannot?
Also my calculated field for calculating of product total purchase amount in products DB Table doesn't seems to be functioning too?


Please see the attached sample project.

Post's attachments

Attachment icon ShowingProdForSupp.zip 10.68 kb, 396 downloads since 2018-02-05 

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

2 Reply by derek (edited by derek 2018-02-05 11:49:19)

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

Re: Getting data on grid from different tables and grids?

Hi Adam,
There were a few things that I changed.
1.  you were using tgmainproducts instead of tgmainsuppliers to display products belonging to a selected supplier.
2.  you were holding qty, id_product and unitprice on your purchinv table which doesn't make sense to me - these are things that you'd store at the purchinvitem level;  if you have an invoice with 5 lines of different products, what does 'qty' mean, which of the 5 id_products are you going to hold etc etc?  Alternatively, if your application only allows for single line invoicing, then there is no point to having both a purchinv and a purchinvitem table.
3.  added calculated fields to hold net values (as they can all be derived from source data (qty and unit price)) rather than doing it by script.
I haven't changed it but I'd consider using some sort of relational lookup on the purchinvitem form otherwise you can easily be selecting products that haven't been set up for the supplier that you're invoicing - with all of the knock-on problems that will cause.
Derek

Post's attachments

Attachment icon ShowingProdForSupp changed.zip 343.66 kb, 399 downloads since 2018-02-05 

3 Reply by AD1408 (edited by AD1408 2018-02-05 16:26:10)

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

Re: Getting data on grid from different tables and grids?

Hi Derek,


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


I used older sample project where all inv fields were on same table. My mistake, I forgot to delete qty and unit price fields from PurchInv table after creating PurchInvItem table.


... I'd consider using some sort of relational lookup on the purchinvitem form otherwise you can easily be selecting products that haven't been set up for the supplier that you're invoicing - with all of the knock-on problems that will cause.

You are absolutely right. That's definitely needed.


I have added disabled supplier combo on PurchInvItem form and copied combo value from PurchInv supplier combo:

procedure frmPurchInvItem_OnShow (Sender: TObject; Action: string);
begin
frmPurchInvItem.cbPurchInvItemSupplier.dbItemID := frmPurchInv.cbPurchInvSupplier.dbItemID;
end;

I was thinking adding on change event for PurchInvItem supplier combo onChange event to get product combo on PurchInvItem lists only the products belonging supplier showing on supplier combo:

procedure frmPurchInvItem_cbPurchInvItemSupplier_OnChange (Sender: TObject);
begin
frmPurchInvItem.cbPurchInvProduct.dbItemID := SQLExecute('SELECT name FROM product WHERE id = '+frmPurchInvItem.cbPurchInvItemSupplier.sqlValue);
end;

However, it didn't work. My one cell brain couldn't make the correct connection here.

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

4 Reply by derek (edited by derek 2018-02-06 01:28:14)

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

Re: Getting data on grid from different tables and grids?

Hi Adam,
I'm a great believer in making life easy for myself (LOL!) so I'd simply use the standard 'parentcombobox' functionality.
This normally works without any script.  However, because you are passing the supplier through from frmpurchinv to frmpurchinvitem by script rather than manually entering it on frmpurchinvitem, you need to add a 'doonchange' line (it's in the attachment).
Hope this helps,
Derek.

Post's attachments

Attachment icon ShowingProdForSupp changed2.zip 344.51 kb, 373 downloads since 2018-02-06 

5 Reply by AD1408 (edited by AD1408 2018-02-06 03:41:44)

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

Re: Getting data on grid from different tables and grids?

Hi Derek,


Great stuff.................
Thank you very much for your kind and prompt help...............
Truly appreciated............................


'doonchange' and use of  'vname: string' in procedure are new for me. I've put them in my reference library.


Ps/. Is it possible to convert the following calculated field to form script?

(
SELECT sum(PurchInvitem.unitprice * purchinvitem.qty) 
FROM PurchInvitem
WHERE PurchInvitem.id_Product=Product.id
)

something like (which doesn't work)

procedure frmProduct_OnShow (Sender: TObject; Action: string);
begin
frmProduct.edPurchTotal := frmPurchInvItem.edPurchInvQty * frmPurchInvItem.edPurchInvUnitPrice.Value;
end;

I like to use script instead of calculated field in order to avoid decimals formatting script for grid display.
I've done the invTotal but couldn't do purchTotal.

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: Getting data on grid from different tables and grids?

Hi Adam,
The 'vname: string' was just a local variable that got included in some copy/paste I was doing - it's not needed.  It's just the 'doonchange' that's required.
How exactly are you wanting the calculated fields to display in the grid (ie left or right justified, how many decimal places etc) because you can do a fair amount with 'printf' when you define your calculated fields?
Derek.

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

Re: Getting data on grid from different tables and grids?

Hi Derek,


Thanks a lot....
On formatting, just standard currency formatting.. right justified with two decimal places.

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

8 Reply by derek (edited by derek 2018-02-06 16:22:33)

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

Re: Getting data on grid from different tables and grids?

Hi Adam,
I think I'd just leave the calculated fields as they are and do the formatting in the script - something like
tnxnumbercolumn(form1.tablegrid1.Columns[1]).formatmask := '#,###,##0.00'; 
Please see the attached.
Derek.

Post's attachments

Attachment icon ShowingProdForSupp changed.zip 345.27 kb, 458 downloads since 2018-02-06 

9 Reply by AD1408 (edited by AD1408 2018-02-07 07:13:06)

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

Re: Getting data on grid from different tables and grids?

Hi Derek,


Thank you very much.......................


I know about currency grid column formatting. I was trying to avoid using that method as it requires more lines of script depending on how many different grids used. Additionally, when grid column order is changed we need to go back to script, locate it and change column numbers too.
I was looking for solution of make it once and use it in many places approach. Perhaps that's not possible with current MVD.


If and when Dmitry updates older and not supported NextGrid in current MVD to current NextGrid there may be solution to currency decimals display in respect of calc fields and few other bugs/glitches exist.


Once again, thank you very much for your kind help and guidance Derek.........................

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