Master Detail - Updating data in both both files

Developers Programming
#1

Please some help.

I have a Master Detail Form
Master File Name -> customers
Detail File Name -> cptecli

From the master file I have 1 field to update with data from the detail file

The field name in the master file to update is -> BalanceCpteGdes
the following fields are found in the detai file -> journalID, CustID, INV_NO, DEBIT, CREDIT, BALANCE

in the deail file once open, either the DEBIT OR THE CREDIT FIELD is captured from the keyboard by the user

I setup the following METHOD -> update_cpte() // call from AfterUpdate

sc_lookup(dataset,“SELECT
BalanceCpteGdes
FROM customer
WHERE CustID = {CustID}”);

$actualbalance = number_format({dataset[0][0]}, 2, ‘.’, ‘,’);
$newBalanceGde = $actualbalance + {DEBIT} - {CREDIT};
{BALANCE} = $newBalanceGde;

sc_lookup(dataset,“SELECT
SUM(DEBIT), SUM(CREDIT)
FROM cptecli
WHERE CustID = {CustID}”);

$totdebit = number_format({dataset[0][0]}, 2, ‘.’, ‘,’);
$totcredit = number_format({dataset[0][1]}, 2, ‘.’, ‘,’);

sc_master_value(‘BalanceCpteGdes’, BalanceCpteGdes + $totdebit - $totcredit);

It does not work - the field in the master is not being updated
nor are the BALANCE field in the detail file.

Is this type of coding Ok for what I need to do?

Please advise

#2

Re: Master Detail - Updating data in both both files

I have never used the built in functions for M/D in SC. The other alternative is to just perform the SQL updates yourself in the events using sc_exec_sql() and not use the other code it seems to take to get sc_master_value() to work.

Perhaps you found a bug if your code does not work.

Regards,
Scott.

#3

Re: Master Detail - Updating data in both both files

I found that you may have syntax error
e.g. you use — $totcredit = number_format({dataset[0][1]}, 2, ‘.’, ‘,’);
you can modify to {dataset}[0][1]
please try put { } to quote your variable

sc_master_value(‘BalanceCpteGdes’, {BalanceCpteGdes} + {$totdebit }- {$totcredit)};

#4

Re: Master Detail - Updating data in both both files

Hi ScottMartin - can you give me an small example - it not quite clear to me the example in the macro section as mentioned below

sc_exec_sql(“sql command”, “connection”)

sc_exec_sql ({field}/“sql command”, “connection”)

#5

Re: Master Detail - Updating data in both both files

As for you KH Victor - when I change the code to

sc_lookup(dataset,“SELECT
BalanceCpteGdes
FROM customers
WHERE CustID = {CustID}”);

$actualbalance = {dataset[0][0]};
$newBalanceGde = $actualbalance + {DEBIT} - {CREDIT};
{BALANCE} = $newBalanceGde;

sc_lookup(dataset,“SELECT
SUM(DEBIT), SUM(CREDIT)
FROM cptecli
WHERE CustID = {CustID}”);

$totdebit = {dataset[0][0]};
$totcredit = {dataset[0][1]};

sc_master_value(‘BalanceCpteGdes’, {BalanceCpteGdes} + {$totdebit} - {$totcredit});

I get the following errors

Transactions not supported in ‘mysql’ driver. Use ‘mysqlt’ or ‘mysqli’ driver

(mysql): select count(*) from cptecli where CpteCliID = 1

(mysql): UPDATE cptecli SET CustID = 1, DATE = ‘2010-03-28’, INV_NO = 1, TRANSACT = ‘test’, DEBIT = 500.00, CREDIT = 0.00, BALANCE = 0.00, DEBIT_US = 0.00, CREDIT_US = 0.00, BALANCE_US = 0.00 WHERE CpteCliID = 1

(mysql): SELECT BalanceCpteGdes FROM customers WHERE CustID = 1

(mysql): SELECT SUM(DEBIT), SUM(CREDIT) FROM cptecli WHERE CustID = 1

#6

Re: Master Detail - Updating data in both both files

Are you using MySQL database?

The following statement is I used in my program.
Please have a close look at it, you have used wrong syntax.

sc_lookup(dataset, “SELECT round( sum( a.ratio * b.std_cost ) , 2 )
FROM bom a
INNER JOIN item b ON a.sub_item_code = b.item_code
WHERE a.item_code = ‘{item_code}’”);
{w_bom_std_cost}={dataset}[0][0];

  1. You have to quote ’ ’ the variable in the where statement.
  2. it should be {dataset}[0][0] instead of {dataset [0][0]}

I will modify your statement as follows

sc_lookup(dataset,“SELECT
BalanceCpteGdes
FROM customers
WHERE CustID = ‘{CustID}’”);

$actualbalance = {dataset}[0][0];
prompt Actual Balance ;".{dataset}[0][0];
// the prompt statement helps you to check whether you get the desiared result correctly step by step

$newBalanceGde = $actualbalance + {DEBIT} - {CREDIT};
{BALANCE} = $newBalanceGde;

#7

Re: Master Detail - Updating data in both both files

sorry the prompt statement should be

prompt "Actual Balance ".{dataset}[0][0];

#8

Re: Master Detail - Updating data in both both files

One quick note, this syntax is wrong: {dataset}[0][0]
should be: {dataset[0][0]}

On the sc_exec_sql(). Just standard SQL

$sql = ‘UPDATE table SET field = “value” WHERE keyfield = keyvalue’;
sc_exec_sql($sql);

The connection param is optional, but it refers to the connection created for the project.

Regards,
Scott.

#9

Re: Master Detail - Updating data in both both files

Thanks for the support KH Victor and ScottMartin !

Here is the code I am using now because of your help … and it works perfect
of course I am going to try ScottMartin 's option of using sc_exec_sql(“sql command”, “connection”)

sc_lookup(dataset,“SELECT
BalanceCpteGdes
FROM customers
WHERE CustID = {CustID}”);

$actualbalance = {dataset[0][0]};
$newBalanceGde = $actualbalance + {DEBIT} - {CREDIT};
{BALANCE} = $newBalanceGde;

sc_lookup(dataset,“SELECT
SUM(DEBIT), SUM(CREDIT)
FROM cptecli
WHERE CustID = {CustID}”);

$totdebit = {dataset[0][0]};
$totcredit = {dataset[0][1]};

sc_master_value(‘BalanceCpteGdes’, $actualbalance + $totdebit - $totcredit);