[John Boyce]

Thanks, Bill. Our database is a little bigger (45GB), but not a lot. Also, we run Raid 10 using 15k disks, so speed shouldn’t be an issue.

John

[John Boyce]

Bill,

Thanks for the instruction. How long did it take to run, and approximately how big is your database? Component Control have said that the process to upgrade will take about 2 days, which seems like a long time.

John

[John Boyce]

Dave,

Prices are not a 1 to 1 relationship with Parts Master. You can get all of the data from price records connected to any stock lines by the following:

Select pnm.PN, pnm.DESCRIPTION, pri.UNIT_PRICE, stm.STOCK_LINE

from parts_master pnm join prices pri on pnm.PNM_AUTO_KEY = pri.PNM_AUTO_KEY

left join stock stm on pnm.PNM_AUTO_KEY = stm.STM_AUTO_KEY

where pnm.PN = ‘ABCD’

or if you want to connect to a specific stock line and know which one it is substitute

where pnm.PN = ‘ABCD’ and stm.STOCK_LINE = 1

John

[John Boyce]

Bill,

I just extracted that code from the Crystal Report. This variation should work with Oracle.

SELECT AR_ACCOUNT.AR_CONTROL_NUMBER, AR_ACCOUNT.COMPANY_PO_NUMBER, AR_ACCOUNT.ENTRY_DATE,

TERM_CODES.DUE_DAYS, AR_ACCOUNT.BALANCE, INVC_HEADER.TOTAL_PRICE,

COMPANIES.COMPANY_CODE, COMPANIES.COMPANY_NAME, COMPANIES.ADDRESS1, COMPANIES.ADDRESS2, COMPANIES.ADDRESS3,

COMPANIES.CITY, COMPANIES.STATE, COMPANIES.ZIP_CODE, COMPANIES.COUNTRY, COMPANIES.CONTACT_NAME, COMPANIES.SITE_CODE

FROM AR_ACCOUNT INNER JOIN COMPANIES ON AR_ACCOUNT.CMP_AUTO_KEY=COMPANIES.CMP_AUTO_KEY

LEFT OUTER JOIN INVC_HEADER ON AR_ACCOUNT.AR_CONTROL_NUMBER=INVC_HEADER.INVC_NUMBER

INNER JOIN TERM_CODES ON AR_ACCOUNT.TMC_AUTO_KEY=TERM_CODES.TMC_AUTO_KEY

WHERE COMPANIES.COMPANY_CODE=’006K’ AND COMPANIES.SITE_CODE=’_’ AND

AR_ACCOUNT.BALANCE<>0 AND AR_ACCOUNT.ENTRY_DATE < To_Date('2013-11-01', 'YYYY-MM-DD'); John

[John Boyce]

Bill,

This is the SQL statement we use from within Crystal Reports to run A/R Statements. The unique selector is company code + site code.

SELECT “AR_ACCOUNT”.”AR_CONTROL_NUMBER”, “AR_ACCOUNT”.”COMPANY_PO_NUMBER”, “AR_ACCOUNT”.”ENTRY_DATE”,

“TERM_CODES”.”DUE_DAYS”, “AR_ACCOUNT”.”BALANCE”, “INVC_HEADER”.”TOTAL_PRICE”,

“COMPANIES”.”COMPANY_CODE”, “COMPANIES”.”COMPANY_NAME”, “COMPANIES”.”ADDRESS1″, “COMPANIES”.”ADDRESS2″, “COMPANIES”.”ADDRESS3″,

“COMPANIES”.”CITY”, “COMPANIES”.”STATE”, “COMPANIES”.”ZIP_CODE”, “COMPANIES”.”COUNTRY”, “COMPANIES”.”CONTACT_NAME”, “COMPANIES”.”SITE_CODE”

FROM {oj ((“AR_ACCOUNT” “AR_ACCOUNT” INNER JOIN “COMPANIES” “COMPANIES” ON “AR_ACCOUNT”.”CMP_AUTO_KEY”=”COMPANIES”.”CMP_AUTO_KEY”)

LEFT OUTER JOIN “INVC_HEADER” “INVC_HEADER” ON “AR_ACCOUNT”.”AR_CONTROL_NUMBER”=”INVC_HEADER”.”INVC_NUMBER”)

INNER JOIN “TERM_CODES” “TERM_CODES” ON “AR_ACCOUNT”.”TMC_AUTO_KEY”=”TERM_CODES”.”TMC_AUTO_KEY”}

WHERE “COMPANIES”.”COMPANY_CODE”=’006K’ AND “COMPANIES”.”SITE_CODE”=” AND

“AR_ACCOUNT”.”BALANCE”<>0 AND “AR_ACCOUNT”.”ENTRY_DATE”<{ts '2013-11-01 00:00:00'} John

[John Boyce]

Tony,

One reason to construct the query as Pietro has is that multiple joins are usually more efficient than sub-queries, though that can vary depending on how many records are being queried. In Paul’s example for each stock line returned the system is executing six additional queries.

In Pietro’s example, you need to be cognizant of the difference between inner joins and left joins. Inner joins require a match to return a record, while left joins only require the left side of the equation. Subqueries are the equivalent of left joins, so my guess is that if you changed all of the inner joins to left joins you would get the same result as Paul’s query.

Bottom line, whatever gets you the result that you want is the best query for you.

John

[John Boyce]

If you are interested, I’ve posted a program on the Que Group site that does scheduled Firebird backups, compresses the Quantum file, can copy to one or two remote destinations via the copy command or FTP, automatically deletes previous copies over X days old and emails to a user or list of users a log of the backup activity. Actual downtime of the Quantum database is only one or two minutes depending on your database size.

Let me know if you would like to know more.

John Boyce

[John Boyce]

Have you tried the following:

Under Database/Set Datasource Location you link to the new datasource in the Replace With box. Then click the Update button. If the datasources are identical (for example two ODBC connections to similarly structured databases (Quantum vs Quantum_Test), the new source will replace the previous one and you just save the report.

If they are not identical, you get a box that allows you to map the source of each new field to the one currently on the report from the previous source.

John

[John Boyce]

Tony,

We looked into their web services a few years ago. Very powerful, but also complex. It would have been an extensive programming exercise and we decided that it was not worth the effort at the time, partly because ILS and CC were then in discussions for better integration. Also, their documentation was not very extensive. I also discussed with a former employee of ours, who is now a consultant and programmer, and he had difficulties with the API in it’s complexity.

This was several years ago, though, so things may have changed.

John

[John Boyce]

Ken,

My understanding was that the switches were complementary. If your active stock is being deleted from the Stockmarket for those parts I suggest calling CC to get clarification.

John

[John Boyce]

Ken,

If you edit the part number there is a section with Checkboxes for Upload Part Capabilities to Stock Market, and then below separate checkboxes for Manufacturing, Overhaul, Repair, Certification and Distribution. You can check as many as you wish for each part.

There is probably a pretty simple update query you could do if you wanted to update a large number of parts at once.

John

[John Boyce]

Spencer,

Feel free to give me a call to get a better understanding of what you are trying to do. I’d be happy to discuss options available to you.

John Boyce

818-362-1100 x264

[John Boyce]

I think in addition to paying for the Form Designer Module, you could engage CC to create an additional form for you. If it’s simply changing the label ‘Exchange’ for ‘Loan’ that should be a minor exercise, and cost effective.

However, if you do many modifications of the forms you’ll soon pay more than the purchase price of the Forms Designer.

[John Boyce]

I don’t use the screen scripter, but in Delphi you would assign the Combo Box selection to the dataset of the table/query that you are updating in the database. This is different from your dataset Q which is only the list of users in the drop down list.

For example:

If the table is SM_Header and the datasource is DataSource2 then the following would apply:

SqlTable1.TableName := SM_Header;

DataSource2.DataSet := SqlTable1;

procedure TForm.CmbClick(Sender: TObject);

Begin

SqlTable1.Edit;

SqlTable1.FieldByName(‘SDF_SMH_010’).AsString := cmb.Text;

SqlTable1.Post;

End;

This is done with a simple TComboBox rather than a TDBComboBox. I’ve had some problems with the TDBComboBox events that don’t show up with the TCombobox for some reason.

[John Boyce]

Our database is about 9 GB with no problems. As for deleting records from the AUDIT_TRAIL table in Firebird; there are no constraints or triggers that prohibit deletions. Other than losing the data, I don’t think there will be a problem in deleting transations. To reclaim the space, you will have to do a backup and restore after the deletion.

[John Boyce]

I believe that the information regarding licensed modules is kept in the encrypted licence file, rather than any particulary table. Suggest that you call CC to confirm where the info is kept.

[John Boyce]

In Firebird the userid and password in Quantum are the same as in the Firebird security file. As the Firebird security takes precedence over Quantum security you could limit access through Firebird security to read only for each metadata object (table, procedure, etc.) through SQL Grant and Revoke statements.

In Oracle it’s a bit different as CC uses a single userid to connect to the database and then a separate sys_user table with userid’s and passwords for Quantum privileges, so you would need a separate schema with read only privileges.

[John Boyce]

I agree with Kevin. I did a quick mod to our check form for testing, and adding the vendor code took about 10 minutes. You do need the forms designor.