Find table by column name

Quite common task, which I bored to google and as it is not hard to memorize, but decided to record it.

SELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS  WHERE COLUMN_NAME LIKE '%DocType%'

List View in accumatica

Here I want to describe how to create such simple page like this:

For reading this manual father you need to know how to add page to sitemap in accumatica. If you need me to describe this process let me know, I assume it is not challenging process.

For staff like this you need ListView template and two other classes.

The first class is used to represent single view item in grid and second class intended for navigating in the db. 

Accumatica manual recomends the following location of those two classes:

1. Create separated project ( for example IG )

2. Inside of it create Folder with the name of pages folder ( for example Investigation )

3. Add reference to the dll PX.Data

4. Create class CountryMaint:

namespace IG.Investigation
{
    public class CountryMaint : PXGraph<CountryMaint>
    {
    }
}

5. Build class library and add reference to newly created project.

I got something similar to this ( just without red rectangle ):

For now we have manager of records but without representation of records itself. Let's do it with tool called Data access class generator. Switch back to the file IG301000.aspx choose design mode and for the typename choose IG.Investigation.CountryMaint. 

Press at generate class and in the window that will appear type Country.

Here is what I seen:

Press at "Generate" button and you'll see that class which can represent single record was created exactly in DAC folder.

Now if will have desire to open developed web page you'll notice 

Error #96: View  doesn't exist.

which means that we need to add something, that is called view. What it can be? Add to the class following line

public PXSelect<Country> Countries;

and Visible="true" and then try to open page again:

Error #96: View  doesn't exist.

Why? I went nuts tring to figure out, untill I watched in manual, which sad the next phrase: 

In the Properties window, set the following property for the grid:

• DataMember:Countries

But why it speak about non-mentioned DataMember as View ? I have no idea, but if you have share it.

So, let's set it and execute. Are you ready to see result?

If to put simly this warning means that you need to add method Save to the CountryMaint class. But why Accumatica doesn't show anything I have no idea.

Let's add it:

public class CountryMaint : PXGraph<CountryMaint>
    {
        public PXSelect<Country> Countries;
        public PXSave<Country> Save;
    }

and open the page:

Cool, isn't it? Button save is ready. 

Let's add some fields for displaying. It can be done at PXGrid, via Edit content layout link:

Then you just add fields CountryCD and description. I'll omit this process because IMHO it is not complicated. Execute page again and you'll see list of countries. Enjoy !!!!

Delete all tables from db in ms sql

For purpose of deleting all tables in MS SQL fitting is:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

This is a hidden stored procedure in sql server that will be executed for each table in the database you chosen.

Also here described how to imitate drop in db 

Acumatica date time specifiers

Hello everybody,

today I want to describe some date time specifiers in acumatica.

d - is default format pattern, so if you intend to use short date pattern, you can just ommit pattern in usage function

public static System.Text.StringBuilder MakePattern(string format, System.Globalization.DateTimeFormatInfo df)

D - long date pattern. Something similar to "Thursday, April 10, 2008"

F - long time pattern. Full date/time pattern. Something similar to "Monday, June 15, 2009 1:45:30 PM".

G - long time pattern. General date time pattern. Something like  "6/15/2009 1:45:30 PM" for "en-US" or "2009/6/15 13:45:30" for zh-CN

M equals to m and means Month day pattern "June 15"

R equals to r - RFC 1123 pattern. The custom format string is "ddd, dd MMM yyyy HH':'mm':'ss 'GMT' like "Mon, 15 Jun 2009 20:45:30 GMT"

T - long time pattern. "1:45:30"

U - long time pattern. Universal full date/time pattern. "Monday, June 15, 2009 8:45:30 PM" or "den 15 juni 2009 20:45:30" ( for sv-SE )

s - sortable date time pattern. Intended for sorting as strings. Format: "yyyy'-'MM'-'dd'T'HH':'mm':'ss". Looks like "2009-06-15T13:45:30"

t - short time pattern. 1:45 PM ( for en-US ), "13:45" ( hr-HR )

u - universal sortable date time pattern. 2009-06-15 20:45:30Z. Also for sorting as string.

y equals to Y year month pattern. Looks like June, 2009

f - short time pattern. Similar to "Monday, June 15, 2009 1:45 PM". Differs from 'F' by ommiting seconds

g - short time pattern ( at least in code ), and looks like this:

case 'g':
                    builder.Append(df.get_ShortDatePattern()).Append(' ').Append(df.get_ShortTimePattern());
                    return builder;

I was surprised because here g stands for "General date/time pattern" or  "6/15/2009 1:45 PM" while in acumatica 'g' is equal to 'f'

 

Acumatica form elements types

Acumatica has following types of input elements for it's users:

• TextEdit: The user enters text in an input field. This is just simple text box implemented as <input type="input".... />

 • NumberEdit: The user enters a number  in an input field.

• Selector: The user  selects a value from a list of elements in the database. If to look at the html, it is implemented as <input type="input".... /> plus div.

• Selector with Description: The user selects a value from a list of elements in the database; the UI element  description is based on the  data selected from a specific database field.

• ComboBox: The user selects a value from a list of elements composed of a set of options predefined in the business logic or data access class, with the possibility  to type data into the editing combo box.

• CheckBox: The  user selects  or clears the option. 

• MaskEdit: The user enters values (text or numbers) with the predefined format in the input field.

• SegmentMask: The user enters values (text or numbers) with the predefined format in the input field; the masking rules, which define the input format, can be set individually for each segment of the input string.

• LinkEdit: The user enters a webpage URL within the input field; the entered data is verified for the correct URL format. 

• MailEdit: The user enters an email address within an input field; the entered data is verified for having the correct email address  format.

• GroupBox: This element is  used to visually group a set of buttons. 

• DateTimeEdit: The user selects the date by using a visual tool. 

• ImageUploader: The user can upload an image file to the selected area of a form.

I have plans to describe some of the parameters later.

 

 

How to get formatters of acumatica with reflector

During reading manual of acumatica for T100 test I faced control PXMaskEdit. That is wonderful control because it allows you to make masked input. I was very happy, but was unable to realize how to customize characters for entering values. Search in pdf file didn't give me satisfactory results so I decided to decipher masking fields with help of google, and wasn't successfull with search results. So I made next step and used my favorite tool reflector. 

In the window of reflector I typed segmentmask and below you can see screenshot of what I found:

 

Little bit of scrolling opened in front of my eyes the following picture:

                            case '9':

                                item.EditMask = MaskType.Numeric;

                                break;

                            case '?':

                                item.EditMask = MaskType.Alpha;

                                break;

                            case 'C':

                                item.EditMask = MaskType.Ascii;

                                break;

                            case 'a':

                                item.EditMask = MaskType.AlphaNumeric;

                                break;

And this brought me to the following conclusions:

a. If you want in some particular place to be inputs of numbers you can use 9. For example (999)-(999)-99-99 will give you the following input mask: (___)-(___)-__-__

b. I assume that "?" could stand for alphabetic that matches only a-z, not symbols like: > !@#$%/[]{}(), etc.

c. C or Ascii symbols. I think it's understandable for what it stands for :)

d. a or Alpha numeric. I think it stands for symbols a-z and numbers 0-9.

 

 

 

MS SQL find table by data record

Some time it happens that you need to find some string in the MS SQL database. 

Here I found very helpful ms sql code which helps to find any string in database.

At stackoverflow I found optimization of that script, which I copy/pasted below:

 

CREATE PROC SearchAllTables
(
    @SearchStr nvarchar(100)
)
AS
BEGIN

-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.
-- Purpose: To search all columns of all tables for a given search string
-- Written by: Narayana Vyas Kondreddi
-- Site: http://vyaskn.tripod.com
-- Tested on: SQL Server 7.0 and SQL Server 2000
-- Date modified: 28th July 2002 22:50 GMT

DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL
BEGIN
    SET @ColumnName = ''
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM    INFORMATION_SCHEMA.COLUMNS
            WHERE       TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND TABLE_NAME  = PARSENAME(@TableName, 1)
                AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                AND QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL
        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END 
END

SELECT ColumnName, ColumnValue FROM @Results
END

Find screen

In case if you look at something in acumatica and want quickly to find sources of that window in resharper you can do the following:

1. click in the command line, and you'll see something like this:

just withoug red rectangle.

2. Switch to your Visual studio and navigate to page gl201000.aspx or gl201000.aspx.cs. In case if you have resharper in your Visual Studio you can navigate to file in a window like this:

Acumatica

Here is just to anounce that the first post in my blog will be about acumatica. If to speak in general acumatica is ERP which from programmers viewpoint is like CMS which is intended for management of the resources at company. It is much cheaper then SAP and it also tries to become competitor of AXAPTA. So, lets see what will be the outcome.