1. Home
  2. Point-Of-Sale Software
  3. Very Advance Alias Report

Very Advance Alias Report

Currently have 2 reports that I run to get the results I need. I have to send a complete updated inventory to my Gift Registry program every few months. To do this I have a report that finds me all items with an Alias, another report that finds all items with no barcodes or anything in the alias. We have some items that have multiple aliases and our company that does our registry program must have a nice clean excel file with either a 12 digit barcode or no barcode at all. So I have been taking my 2 reports and putting them together in 1 excel file then I run a formula that finds all duplicate item lookup codes and turns them red. Then I manually go through the file looking for items that are listed more than once and delete the duplicate items with a wrong alias. What I would like is to have a report that will list all of my items once and yet be able to list the items that have an alias of 12-digits and list all of the other items that have a weird alias or no alias. But still have each item only listed once on the report (So the 12-digit Alias would take priority over any other Aliases associated with the item). I think it is impossible for a report to do this since I have tried and tried. But some people on this Newsgroup are nothing short of pure genius. Thanks Everyone!

Neil

Reply to
nt8378
Loading thread data ...

Reply to
Brad

Thank you for your reply Brad,

The wierd alias would be any alias that is not 12-digits long. As for creating a add-on that would perform my desired results and pump them into an Excel file. That is way beyond me. I wish I could be that good. I appreciate your advice.

Thank you, Neil

"Brad" wrote:

Reply to
nt8378

Reply to
Brad

I don't know about a report, but you could do this fairly easily with Excel.

To find all aliases that are fewer than 12 characters, run & export the following query: SELECT * FROM Alias

Open the exported file with MS Word. Select All & Copy. Paste Special... Text into Excel. In a new column, enter this formula: =LEN(Alias) fill this formula down the entire list. Sort the entire table by the LEN column. It should be easy to identify the entries where LEN 12.

NOTE: The 'Paste Special... Text' step is CRITICAL. If you open the file with Excel, any Alias with leading zeroes (and there are probably lots) will lose the leading zeroes and screw up everything. MAKE ABSOLUTELY CERTAIN THAT YOUR LEADING ZEROES HAVE BEEN PRESERVED.

If you want to delete the LEN12 entries, in another column enter: ="DELETE from Alias WHERE ID = "&ID where ID is a reference to the cell containing the Alias ID of the LEN12 Alias.

Fill this formula down for all of the offending cells. DON'T GO TOO FAR, or you will delete valid Aliases in the next step...

WARNING: The following procedure is not for the faint of heart or technically clumsy. PROCEED AT YOUR OWN RISK!

Wait until the store is closed. Backup your DB. Copy the entire series of DELETE query statements into a new query window in SO Admin. Run the query.

You have now deleted every Alias with a length other than 12 characters.

If you save the spreadsheet, you can probably turn this process into a

10-minute thing.

Tom

Reply to
Terrible Tom

OK, my prior reply was the REALLY long way around.

Try this: DELETE from Alias WHERE LEN (Alias) 12

As with any table-modifying query, you should always backup your database.

Tom

Reply to
Terrible Tom

Reply to
Craig

BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.