1,876

(0 replies, posted in Accounts Payable)

Taking the sql backup diffs between before and after adding a single Supplier, the following sequence of SQL statements are observed (Assumed TB_PREF=3_):

SET @Supplier  := "Finger Chips Intl.";
SET @SuppShort := "FCI";
SET @Contact   := "Jackson";
SET @Phone     := "568974521";
SET @EMail     := "supplier1@example.com";
SET @Website   := "http://www.fci.com";
SET @Address   := "Alpha Street,\nGladystown - 654543";
SET @GST       := "8954587411";
SET @DefCurr   := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="curr_default" AND `category`="setup.company");
SET @CredAc    := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="creditors_act" AND `category`="glsetup.purchase");
SET @PayDsctAc := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="pyt_discount_act" AND `category`="glsetup.purchase");

INSERT INTO `3_suppliers` VALUES
(NULL, @Supplier, @SuppShort, @Address, '', @Phone, '', '', @Website, '', @DefCurr, '4', '0', '0', '0', '1', '0', '', @CredAc, @PayDsctAc, '', '0');
SET @SupplierID := (SELECT LAST_INSERT_ID());

INSERT INTO `3_crm_persons` VALUES
(NULL, @SuppShort, @Contact, NULL, @Address, @GST, NULL, NULL, @EMail, NULL, '', '0');
SET @PersonID := (SELECT LAST_INSERT_ID());

INSERT INTO `3_crm_contacts` VALUES
(NULL, @PersonID, 'supplier', 'general', @SupplierID);

Here, first a Supplier is created.
Then a Person is created.
Finally, this new  Person is assigned to the new Supplier as a Contact.

The above can be useful for bulk importing of suppliers from disparate systems.

1,877

(9 replies, posted in Installation)

The file en_US.mo should not be renamed to .po. Instead remove the entire en_US folder if you want or just add in the en_US.po file for your records by either generating it from the .mo file or copying it over from here (ISO-8859-1) or from here here (UTF-8).

I think it is for puritanism only. The reset should be sufficient for now. If any third-party careless code called it, possibly it might go into negative.

The cancel_transaction() resets the value of the $transaction_level to 0 whenever a script is called in FA, and it happens when the DB connection is setup each time in includes/db/connect_db_mysqli.inc.

@joe: any fixes here?

1,880

(13 replies, posted in Reporting)

The function get_counterparty_name() defined in admin/db/transactions_db.inc is a quite extensive one whereas the alternative used in @BraathWaate's pull request, function payment_person_name() defined in includes/types.inc may not cover all types of transactions.

@joe: will this pull request make for covering all types of transactions required here?
@rafat: can you extensively test @BraathWaate's pull request with your data?

Analysing the diffs of backups before and after adding 1 Customer resulted in a set of INSERT statements when parsed out and sequences with variable extractions done resulted in the following (Assumed TB_PREF=3_):

SET @Customer  := "PKA Enterprises";
SET @CustShort := "PKA";
SET @Phone     := "28557841";
SET @EMail     := "pka@example.com";
SET @Address   := "Joe Smith Road\nTimbuktoo - Z9HG34";
SET @GST       := "1234578";
SET @Location  := 'DEF';
SET @DefCurr   := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="curr_default" AND `category`="setup.company");
SET @Credit    := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="default_credit_limit" AND `category`="glsetup.customer");
SET @DfPayAc   := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="default_prompt_payment_act" AND `category`="glsetup.sales");
SET @PrPayAc   := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="default_sales_discount_act" AND `category`="glsetup.sales");
SET @DrsAc     := (SELECT `value` FROM `3_sys_prefs` WHERE `name`="debtors_act" AND `category`="glsetup.sales");

INSERT INTO `3_debtors_master` VALUES
(NULL, @Customer, @CustShort, @Address, @GST, @DefCurr, '1', '0', '0', '1', '4', '0', '0', @Credit, '', '0');
SET @DrNum := (SELECT LAST_INSERT_ID());

INSERT INTO `3_cust_branch` VALUES
(NULL, @DrNum, @Customer, @CustShort, @Address, '1', '1', @Location, '1', '', @PrPayAc, @DrsAc, @DfPayAc, '1', @Address, '0', '', NULL, '0');
SET @BranchID := (SELECT LAST_INSERT_ID());

INSERT INTO `3_crm_persons` VALUES
(NULL, @CustShort, @Customer, NULL, @Address, @Phone, NULL, NULL, @EMail, NULL, '', '0');
SET @PersonID := (SELECT LAST_INSERT_ID());

INSERT INTO `3_crm_contacts` VALUES
(NULL, @PersonID, 'cust_branch', 'general', @BranchID),
(NULL, @PersonID, 'customer', 'general', @DrNum);

Here, first a customer is created and then a branch for the said customer.
Then one Person is created.
This Person is then allotted to both -  customer and cust_branch.

The above can be useful for bulk importing of customers from disparate systems.

1,882

(13 replies, posted in Reporting)

The Bank Statement only knows Customer and Suppliers as that is where the AR/AP allocations take place. Everything else is a mere Journal kind of Transaction without tracking allocations that can be seen in the GL Account Transactions.

@joe: is this right?

1,883

(4 replies, posted in Accounts Receivable)

The commit has not appeared as yet in the GitHub repo.

@Rmendez confirms that this commit solves his issue.

Committed the Auto Chart of Accounts Generator in my FA24Mods repo.

This generates the standard 4 digit CoA based on the en_US-new.sql file.

Upload the generated Chart to the sql folder and use it to create companies based on it.

Can also access it online.

This method can also be used to generate a skeleton for any new extension as well.

1,885

(4 replies, posted in Accounts Receivable)

The screenshot is self-explanatory. The concerned file is sales/sales_order_entry.php. Whilst the max length of the form field allows 35 characters, the displayed form field is restricted to 10 characters.

In the DB tables, the field sizes are all normally VARCHAR:

reflines.pattern - 35
refs.reference - 100
sales_orders.reference - 100
debtor_trans.reference - 60
purch_orders.reference - TINYTEXT
supp_trans.reference - TINYTEXT
supp_trans.supp_reference - 60
stock_moves.reference - CHAR 40
workorders.wo_ref - 60
wo_manufacture.reference - 100
wo_issues.reference - 100
suppliers_supp_ref - 30 (***less than general form field max of 35***)
cust_branch.branch_ref - 30 (***less than general form field max of 35***)
journal.reference - 60
grn_batch.reference - 60
dimensions.reference - 60
bank_trans.ref - 40

@joe: are there an rationalisations needed amongst these fields and field types.

1,886

(4 replies, posted in Accounts Receivable)

@Rmendez asked:

I am using FA version 2.4.3...............
Can you help me with this??............Please................
"Increase size of Reference field in Direct Invoice"

There are no BEGIN ... COMMIT raw blocks in FA core codebase and possibly not even in any official Extension.

However, local SQL wrapper functions begin_transaction() and commit_transaction() exist and have such a check for non negative values as defined in includes/db/sql_functions.inc.

Create an account for you bank in the chart_master - Banking & GL => GL Accounts
Then assign / link that account as a Bank Account - Banking & GL => Bank Accounts
Thereafter, you can use it in Quick Entries like normally.

@joe: Kindly add RBI into the list of forex providers as appropriate using the list from my function.

At the moment, YAHOO does not provide Forex for FA.
Link: http://203.84.220.151/d/quotes.csv?s=INRLKR=X&f=sl1d1t1ba&e=.csv

ECB provides Forex rates for:

AUD
BGN
BRL
CAD
CHF
CNY
CZK
DKK
GBP
HKD
HRK
HUF
IDR
ILS
INR
ISK
JPY
KRW
MXN
MYR
NOK
NZD
PHP
PLN
RON
RUB
SEK
SGD
THB
TRY
USD
ZAR

Link: http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

GOOGLE works for some currencies and the list of valid currencies differs between access locations.
Link: http://finance.google.com/finance/converter?a=1&from=LKR&to=INR

BLOOMBERG has issues with some currencies like LKR.

https://www.bloomberg.com/quote/LKRINR:CUR

The search for LKRINR:CUR produced no matches. Try the symbol search.

Many symbols available on the Bloomberg Professional Service are not available on Bloomberg.com. To learn more about the Bloomberg Professional Service...

Search the Forum and Wiki first.

https://frontaccounting.com/punbb/viewtopic.php?id=4095
https://frontaccounting.com/punbb/viewtopic.php?id=3863

The template file is in lang/new_language_template/locale.inc which you need to copy over to your language folder  and make your own routines - if they are different from normal English methods on standard translations.

1,891

(2 replies, posted in Report Bugs here)

@joe: Committed

1,892

(1 replies, posted in Reporting)

The TB_PREF is a constant defined as the value of the tbpref array element in the appropriate company# in the config_db.php file. It is the table prefix for your database instance used for the company under reference, generally: 0_, 1_, etc.

All PT_* and ST_* constants among others are defined in includes/types.inc file.

The hard coded date and other parts of the sql are generally taken from the SysPrefs object or from various form fields the scripts interact with.

vTigerCRM community edition is free.

1,894

(11 replies, posted in Reporting)

Unless filtered by a specific voucher type, the number wise sorting will be erroneous as the same number can appear across different dates if they belong to different voucher types.

As the vouchers are sorted by date, all transaction numbers across all voucher types will get listed in the order they were entered by default.

In the case of rep702.php here, the list of transactions are obtained from the function get_gl_transactions() defined in gl/includes/db/gl_db_trans.inc where the sort order in line 176 is: ORDER BY tran_date, counter, the latter being the real sequence element within the transaction date.

vTigerCRM is one way to go where FA's account codes can be inserted into it and the CRM's member id can be inserted into  the chart_master.account_code2 field.

1,896

(3 replies, posted in Setup)

Done - Wiki Updated.

You can change it to whatever you want but remember that easily guessable filenames can be avoided using good entropy as obtains here. Rename it on file download to suit your needs. Consider how the files would stack up when mixed up with similar reports of different companies and / or different fiscal years in the same folder.

The filename for storage is computed in lines 971-974 in reporting/includes/pdf_report.inc:

            // do not use standard filenames or your sensitive company data 
            // are world readable
            $fname = $dir.'/'.random_id().'.pdf';
            $this->Output($fname, 'F');

Mail attachments and inline display will bear the file name provided in the 2nd parameter in the "new FrontReport" class instance's constructor as seen in line 73 of the same file:

    function __construct($title, $filename, $size = 'A4', $fontsize = 9, $orientation = 'P', $margins = NULL, $excelColWidthFactor = NULL)

and instantiated in each repXXX.php file.

Payments must be made and allocated to bills. Bills are generated from one or more sales deliveries. Each Delivery pertains to one or more items of a single sales order.

Make a Direct Invoice for Cash Sales of Tickets.
Make a Credit Sales of Membership with member details and allocate the payment to it.

If you wish to manage the membership separately, the make an extension for it.

The Payment Allocation generates a transaction entry for it in each instance. Hence the Balance Sheet AR/AP part of the Trial balance will only change and not the P&L or it's part of the Trial Balance.

Create an extension for it. Learn from the existing extensions in the FA24extensions repo.