[Mike Carey]

Jeff- unfortunately, you can’t modify or override the sort order for a report, as it is determined by the package that creates the datastream. One option is to recreate the report in Crystal Reports, where you can specify your own sort order for the data.

[Mike Carey]

Brandon- this code will work if the repair detail item is the Main Component or a BOM item in the work order.

var

qc : TOracleDataset;

begin

qc := TOracleDataset.Create(nil);

qc.SetSession;

if RO_DETAIL[‘WOO_AUTO_KEY’] = 0 then

qc.Sql.text :=’select SI_NUMBER from WO_OPERATION where woo_auto_key = (select woo_auto_key from WO_BOM where ‘ + RO_DETAIL[‘WOB_AUTO_KEY’] + ‘ = WOB_AUTO_KEY)’

else

qc.Sql.text :=’select SI_NUMBER from WO_OPERATION where woo_auto_key = ‘ + RO_DETAIL[‘WOO_AUTO_KEY’];

qc.Open;

Text := qc.FieldByName[‘SI_NUMBER’].AsString;

qc.Free;

end;

[Mike Carey]

Faye- I modified the PR Document to print the PR Detail Notes just below the detail line as a DBMEMO field, pointing to the PR Detail feed, Note field.. We are on version 10.9.38.

[Mike Carey]

Jake- thank you! I didn’t think of setting the default program for qid to PDF reader. That helps me a LOT

[Mike Carey]

Jake- How do you create the links to the image files so that they can be viewed / opened ? I have not been able to figure out how to do that as the files have a .qid file extension…

[Mike Carey]

We don’t let our inventory people do inventory adjustments, so I am looking at a way to save the mfg date for a non-stock.

Am considering using the AP Ext Date that is on the PO detail line. Then make a crystal report that shows non-stock items with exp date flag set and PO detail lines with AP ext Date. When the item is used up, then they can open the PO and use the Misc, Ext Ap to remove the date, indicating that it is gone.

Any thoughts on this ?

[Mike Carey]

I found a work-around for this. I use a “hot key” macro program called AutoHotKey all the time and created a macro that will edit the highlighted text field to lower case and remove the underline attribute as well. If anyone is interested in this, let me know.

I also use this hot key program to type out “_auto_key” for me when doing SQL (i just type the PNM and activate the hot key to get PNM_AUTO_KEY. I can never type _auto_key accurately, so this helps a lot. I also have them set up to type common topics i search for in google, like Crystal Reports, Oracle SQL, excel vba, etc..

[Mike Carey]

I see this all the time. It seems to increase from 1 to 2 or 3 whenever Crystal Reports are run, but there may be other circumstances. Even if a user is “logged in 2 times”, they are still only using 1 license, per the “Connection Dashboard” (User, System System, System Options, Connection Dashboard, or the ocDBConnDash.exe program, which uses a connection).

[Mike Carey]

Hi Tim- thank you so much for your help on this. Combining the command SQL statements makes sense. I have used two commands SQL for other reports and never had a problem, so again, thanks for your help ! I am tweaking this a little to show the date each kit was built in the parameter, allow multiple kits to be selected and also no kits to be selected.

Mike

[Mike Carey]

Hello Tim– Here is the report. I prints a pick list for kits being built in a Stock Issue. We build multiple kits in a single stock issue but often cannot build the entire quantity due to part shortages. So the pick list needs to be able to only select the kits currently being picked / built, not the ones previously built.

The woo_auto_key is provided by the “print custom crystal” on the stock issue.

Thank you for any help with this !

[Mike Carey]

The fields that can be hidden or displayed are in the FIELDS table and the fields are connected to the users in the USER_FIELDS table. Is this what you are looking for?

[Mike Carey]

Tino- are you looking for the field name for the “released” checkbox? that is in the WO_OPERATION table, field name RELEASED

[Mike Carey]

Jeff- it sounds like a network issue maybe. Look at how they are connected to your network and that the connection is stable. Something may be timing out or going to sleep or just interrupting the connection possibly. Are the time clock machines on the same network as Quantum server, or RDP or other remote connection ?

[Mike Carey]

In PL/SQL, the statement to fetch the next auto_key for CQH_AUTO_KEY into a variable (NEW_AK):

SELECT G_CQH_AUTO_KEY.NEXTVAL INTO NEW_AK FROM DUAL ;

Then use the value in NEW_AK for CQH_AUTO_KEY on the insert.

Hope this helps…

[Mike Carey]

Tino

Unfortunately, you cannot add tables to Forms Designer reports. They data input streams are fixed and cannot be changed. You have 2 options however.

1- you can convert your report to a Crystal Report, where you have complete control over the data sources.

2- you can pull data from other tables using code to set the value for a field in the report, based on a field in the existing data stream. For example this code will pull the consignment_code field based on the stock_reservation CNC_AUTO_KEY data field.

vAR

q : TOracleDataset;

begin

Text:=”;

q := TOracleDataset.Create(nil);

q.SetSession;

q.Sql.text :=’Select CONSIGNMENT_CODE from CONSIGNMENT_CODES where CNC_AUTO_KEY = ‘ + STOCK_RESERVATIONS’CNC_AUTO_KEY’];

q.Open;

Text := q.FieldByName[‘CONSIGNMENT_CODE’].AsString;

q.Free;

end;

[Mike Carey]

She probably added it as an unverified company. It needs to be verified by someone with verify company authority. unverified companies have limited capabilities until verified, so they show in red.

To verify, click the drop down arrow next to the Company and select “Unverified Companies” (or use the Module menu, Company Management, Unverified Companies. In the Browsing Unverified Companies windows, select the company, then edit it to validate info, add terms, etc etc. Close that window and with that company highlighted in the Browsing Unverified Companies, click the Verify link on the bottom.

[Mike Carey]

The field is in the STOCK table and is called RO_TYPE

select pn, ro_type from stock

where STM_AUTO_KEY = 12397

[Mike Carey]

Ian-

Just to be sure the SQL is correct, run this statement to see if the where clauses are correct. It should return the number of rows that you imported. (this SQL replaces the update with a select count(*) and uses the same where as the update).

Is it possible that you didn’t commit the update after you ran it ?

select count(*) from wo_bom wob

where wob.woo_auto_key=(select wo.woo_auto_key from wo_operation wo where wo.si_number=’MSN1383′)

and wob.lot_req_inspection<>‘T’

[Mike Carey]

Ian-

The security for this is in the Inventory Management section. See screen shot attached. I removed access to Repair Order receiving and it blocks from the RO Global menu. I didn’t test for Core Returns, but hopefully it is in here as well.

[Mike Carey]

I found the issue for the RO printing the header clause smaller than the PO. If you open the RO report in Forms Designer, on the very bottom, click the subHeaderClause: HEADER_CLAUSE to show the design of that sub report. This is where the header clauses in the datastream are processed. Then click / select the memo field for the header clause and you will see that it prints in Arial size 8 font (you can see this at the top in the toolbar or along the left side in the Properties, Text section. Change this to size 10 to match the size that is in the same section for the PO and they will print the same.

The default clauses are added to the RO and PO automatically and print along with any clauses that you add to the header or line item. That’s why you don’t see a section where they print.

[Mike Carey]

Hi Craig

You can use the OCDIA import to do this. Use the STD_MFG_LIST_PRICE_UPDATE import, which allows you to specify the MFG list price and your list price.

Otherwise, to use the Inventory Import, i believe you would need to do the conversion to AUS, use that as your list price, and specify USD as the currency, so that it will convert that back to US dollars.

[Mike Carey]

I heard from TJ that the fix should be ready in about 3 weeks. I will be made available on 10.10 and 10.9.

[Mike Carey]

Hi Dmitri- thank you, i tried that and it still has the problem when printing. Hopefully the fix will come soon from CC.

[Mike Carey]

Do you have the “Large Font” checkbox checked on the report print settings? If that isn’t big enough, you would need to use Forms Designer to modify the report and set the font size larger on the clauses.

[Mike Carey]

the default GL activity, batches plus any custom reports that are set up to print in landscape are the ones that don’t work for us (there are others as well, but these i know don’t work). It always fails for Windows 10 clients, usually fails for Windows 7. The report prints in portrait layout on the page, but since it is designed for 11″ wide, it is truncated at 8.5″.

I heard from CC that the service pack from SAP has been received. They are working on testing and integrating, expecting to be ready in 3 weeks. No word yet as to how the fix will be made available (ie patch to existing, upgrade for 10.9.x, upgrade to 10.10.x).

[Mike Carey]

Yes, using ctrl-F7 this doesn’t allow you to set color using logic. It should at least save the color you specify though- did you do ctrl-enter to save your change?

[Mike Carey]

Hi Abigail…

I suspect your code doesn’t work, because when looking at an item in IC, you are not in an edit form. I played around with this and could not find a way to reference the field to change it’s color here (it seems to work only in the view or edit window. )

If you have screen designer, you can, however, open that screen with the ctrl-f7, select the field and change the color.

here are the color codes that you can use:

clNone Represents no colors (black) $1FFFFFFF

clAqua Aqua $FFFF00

clBlack Black $000000

clBlue Blue $FF0000

clCream Cream $F0FBFF

clDkGray Dark Grey $808080

clFuchsia Fuchsia $FF00FF

clGray Gray $808080

clGreen Green $008000

clLime Lime Green $00FF00

clLtGray Light Gray $C0C0C0

clMaroon Maroon $000080

clMedGray Medium Gray $A4A0A0

clMoneyGreen Mint Green $C0DCC0

clNavy Navy Blue $800000

clOlive Olive Green $008080

clPurple Purple $800080

clRed Red $0000FF

clSilver Silver $C0C0C0

clSkyBlue Sky Blue $F0CAA6

clTeal Teal $808000

clWhite White $FFFFFF

clYellow Yellow $00FFFF

[Mike Carey]

Kryste- If nobody else can see them, maybe security was added to the report ? Highlight the report(s) in Crystal Reports Manager and click the Security menu item along the bottom. If any user(s) are shown in the list, only those people will be able to view and run the report.

Some reports have an option on the print screen to enable/disable crystal report format. This may need to be checked/unchecked for those users.

You can also try to refresh the cache (User menu, system setup, system options, refresh cache. Then see if your users can see the report

[Mike Carey]

We use 14.2.5.2813 and no problems, other than Landscape reports are rotated and cut off for Windows 10 and some Windows 7 users.

[Mike Carey]

Hi Tony

I have been in touch with TJ about this and the latest (about 4 weeks ago) is that the fix from SAP for this was expected towards the end of June. CC then needs to test and verify, then integrate into the versions of Quantum and make a fix available. (All of which takes 10-14 days approx). Hopefully this is happening right now… It’s a problem for us as well.

[Mike Carey]

Hi Abigail —

What is the result if you run the subselect query ?

SELECT PNRANK

FROM RIM_PN_RANK RPR

WHERE PNM.PNM_AUTO_KEY = RPR.PNM_AUTO_KEY

It can only return a single row in order to work in your Update set statement.

[Mike Carey]

I would also be very interested in this topic as well. What tools and techniques are people using both inside and outside of Quantum, what direction is Component Control going with this. Might be a great topic for Que group next year as a round table or mini-presentation.

[Mike Carey]

Great info Jesse ! What kinds of things are you doing with this tool ?

[Mike Carey]

Here is an SQL that will show all report of all kits and their parts. You can turn this into a Crystal Report by pasting this SQL into a command item.

select pnm1.pn Kit_PN, pnm1.description Kit_Desc, pnm2.pn, pnm2.description, kit.qty_item, pcc.condition_code

from parts_master pnm1, parts_master pnm2, kit_components kit, part_condition_codes pcc

where kit.pnm_auto_key = pnm1.pnm_auto_key

and kit.kit_pnm_auto_key = pnm2.pnm_auto_key

and kit.pcc_auto_key = pcc.pcc_auto_key

order by pnm1.pn, pnm2.pn

If you want to look at just a single kit, add this statement just before the Order by

and pnm1.pn = ‘K109788-3-2’

[Mike Carey]

I am very interested in this as well. And how would you assign the correct cost on each part in the kit? I played around with using a Stock Issue and a BOM that defines the kit, but didn’t have enough time to spend working it out.

[Mike Carey]

I think email is a better choice, given that it has to be exact, whereas a name could have many “close” variations- Robert vs Bob, Joann vs Jo Ann, etc

The above query should probably convert both emails to lower or upper case for comparison purposes as well.

[Mike Carey]

Here is the sql to match via email…. you will need to modify in the forms designer code to add your email, cmp_auto_key and UDA field name. I would also consider breaking query into parts for error checking (like multiple rolodex entries for the same email address, as this SQL will blow up the printing if that is the case)

select attribute_value from uda_checked where auto_key = (

select rdx.rdx_auto_key from rolodex rdx, company_rolodex crdx

where rdx.email_address = ‘**email address to lookup’

and crdx.cmp_auto_key = **company cmp auto key**

and crdx.rdx_auto_key = rdx.rdx_auto_key)

and uda_auto_key in (select uda_auto_key from user_defined_attributes where uda_code = ‘**your rolodex UDA name’ and auto_key_prefix = ‘RDX’ )

[Mike Carey]

I think that the issue is that the RDX auto key is not available in the invoice header. You can pick a rolodex entry in the global edit header, but the auto key is not saved, just the values from the rolodex chosen are placed in the header. So if you have defined attributes on the rolodex, there is not a way to get back to them via the invoice header.

[Mike Carey]

Here is the query that feeds that browse.. You can use this to create a report for all companies- just remove the first where condition so that it pulls for all companies and this should work for you.

select inh.INH_AUTO_KEY, inh.INVC_NUMBER, inh.INVOICE_DATE, inh.POST_STATUS, inh.POST_DESC,

inh.TOTAL_PRICE TOTAL_DEPOSIT, inh.FOREIGN_AMOUNT TOTAL_FOREIGN,

inh.TOTAL_PRICE-NVL((select Sum(-UNIT_PRICE) from WO_QUOTE_DETAIL d where d.INH_AUTO_KEY = inh.INH_AUTO_KEY and d.ITEM_TYPE = ‘Deposit’),0) REMAIN_AMOUNT,

NVL(-wqd.UNIT_PRICE,0) APPLIED_AMOUNT, wqd.ENTRY_DATE APPLIED_DATE, wqh.WQH_AUTO_KEY, wqh.WQH_NUMBER,

wqh.POST_STATUS POST_BILLING_STATUS, wqh.POST_DESC POST_BILLING_DESC

from INVC_HEADER inh, WO_QUOTE_DETAIL wqd, WO_QUOTE_HEADER wqh

where inh.CMP_AUTO_KEY = P_CMP

and inh.INVC_TYPE = ‘D’

and inh.TOTAL_PRICE > 0

and wqd.INH_AUTO_KEY (+) = inh.INH_AUTO_KEY

and wqh.WQH_AUTO_KEY (+) = wqd.WQH_AUTO_KEY

and wqd.ITEM_TYPE (+) = ‘Deposit’

order by inh.INVOICE_DATE desc, inh.INVC_NUMBER

[Mike Carey]

This test works without a bunch of nested function calls….

var

fullNumber : integer;

number: double;

decimalInt: integer;

begin

Label4.Text := ‘12.536146’;

number := StrToFloat(Label4.Text);

fullNumber := Trunc(Number);

decimalint := (number – fullnumber + 0.005)*100;

Label5.Text := intToStr(fullNumber) + ‘:’ + IntToStr(decimalint);

{simpler single statement, no variables needed- can eliminate the var declarations above }

Label6.Text := intToStr(Trunc(Number)) + ‘:’ + IntToStr((number – fullnumber + 0.005)*100);

end;

So in your case, Henrik just do the single statement

Text := intToStr(Trunc(WP_MODELS[‘METER_HOURS’])) + ‘:’ + IntToStr((WP_MODELS[‘METER_HOURS’]- Trunc(WP_MODELS[‘METER_HOURS’]) + 0.005)*100);

[Mike Carey]

Henrik- I don’t have a way to test this with your table or date / time format, but I think this will work. (change the field type of decimal to be integer and use IntToStr instead of FloatToStr in the Text statement.

var

fullNumber : integer;

decimal : integer;

begin

fullNumber := Trunc(WP_MODELS[‘METER_HOURS’]);

decimal := (RoundToNDecimals(WP_MODELS[‘METER_HOURS’] – fullNumber, 2) * 100);

Text := intToStr(fullNumber) + ‘:’ + IntToStr(decimal);

end;

[Mike Carey]

Henrik- thank you for catching that i needed to use a 2nd WO_OPERATION table in the SQL. We do 99% main component Repair orders, only a few BOM outside repairs.

I modified your latest version again with a few enhancements- use any or all that you like.

Converted from linked tables with record selector to Command with SQL. Much more efficient to only bring in the records needed instead of all sending all RO data to the client, then tossing out the closed ROs (especially when you have a lot of ROs in your database). Put the work in the SQL is what I have read, for efficiency sake

Open RO are selected based on the RO Status field “status_type” = open, not the RO auto key or RO status description. We have several status’ for RO, but they are all either ‘open’ or ‘closed’ status_type.

If all items for a RO have been received, but the RO is in an open status, a message in yellow is displayed.

The actual stock line repair cost is displayed, along with the RO detail repair cost. It is not always the same (we used deferred receiver and costs can be added or changed by vendor invoice). if the RO cost and actual cost don’t match, the actual cost is displayed in yellow.

Corrrected the code for formula MC_WO, it wasn’t showing properly for SO or BOM linked items.

[Mike Carey]

We are using both wired (usb) and wireless (Bluetooth) scanners with Genericbc.exe with great success. The wireless is used in the parts area, with an Ipad doing remote desktop to a workstation which has the scanner connected via bluetooth.

Here are the 2 items

https://amzn.to/2rqBg83 (wireless)

https://amzn.to/2HR6USR (wired)

[Mike Carey]

Nice report, Henrik. I like your use of colors to indicate status.

Most of our Repair Orders are on a Work Order main component, not a BOM item. I added a left outer join link from RO_DETAIL (woo_auto_key) to WO_OPERATION (woo_auto_key) to get the Work Order Number to show in the far right column.

This report also helps identify a bunch of our ROs that are status of open, but all units have been received, so that we can close them.

[Mike Carey]

To split apart a PDF full of invoices, you can use this tool.. A-PDF Content Splitter It will split the PDF into individual PDF files, and name the files with content from the PDF (ie- each file name is the invoice number). I found it super easy to use.

[Mike Carey]

Lisa- can you post the SQL that you have so far? Are you trying to remove or add SO Reservations from the result?

[Mike Carey]

You could print a date range this way…. On the Invoice module icon drop down menu, there is a Multi Invoice Print. It allows a invoice date and looking at the code the select statement on that function, it selects all invoices >= the date you specify. Flag the invoices you want printed and click the print button on bottom of window

[Mike Carey]

Yes, you are correct. The database field reflects the radio button clicked in the OkButton click routine ! Thank you so much. Now I can prevent data entry errors as they happen instead of after the fact.

[Mike Carey]

I have a Crystal Report that lays out all the codes with indentation to show the hierarchy. It makes a pretty big report, but you could export to PDF or TXT or some other format that is searchable

[Mike Carey]

Thank you Andrew- this will be most helpful indeed !