Inquiry Packs


Mobility ->  Inquiry Packs


These screens allow you to define standard inquiries for use with Ostendo Freeway mobility app. This is in fact an Inquiry report generator allowing you to easily create your own inquiries which can be viewed through the Freeway app. You can define multiple Inquiry Pack names and their associated contents (inquiries) here. Each mobility employee can have an Inquiry pack name assigned. This is done in Employee Mobility Settings - Device tab. 


A short tutorial can be found here: http://ostendo.info/downloads/ostendo/Freeway_Inquiries_Tutorial.pdf




List Tab

Display fields

Pack Name: Name of the Inquiry Pack.

Description: This is a description of the Pack.


Data Display Options

Field Position - On the List screen you can move the field position by dragging the column heading left or right to the position where you want it to appear.

Filtering and Sorting - If you ‘check’ the checkbox then the displayed data is available for filtering and sorting:

  • To sort the data in a selected column simply click on the column heading. Clicking against will sort in descending order.
  • Click on the black ‘down arrow’ to the right of your selected column heading and you can either select a specific entry to display all records containing that entry, or you can select (Custom…) to enter detailed selection criteria.

Searching - If you enter some text into the ‘Search’ field and click on the ‘binoculars’ Icon then all records will be interrogated and those records that contain the entered text will be displayed. To revert back to displaying all records click on the ‘Eraser’ Icon


Buttons

Close: This will close this screen. If you have any unsaved data then you will be asked if you wish to save it before the screen is closed.

Add: This will bring up the ‘Detail’ tab for entry of a new record.

Save: Not applicable to the List panel

Cancel: Not applicable to the List panel

Delete: This enables you to delete the selected record.



Detail Tab

Entry and Display fields

Pack Name: Enter the Name of the Inquiry Pack .

Description: Enter the Inquiry Pack description here.

Authorised for Customer B2B Use : Tick this box to allow B2B customers to use this Inquiry Pack.

Authorised for Sub-Contractor Use: Tick this box to allow Sub-Contractors to use this Inquiry Pack.



Buttons

Close: This will close the screen. If you have any unsaved data then you will be asked if you wish to save it before the screen is closed.

Add: This will bring up a blank ‘Detail’ screen for entry of another record.

Save: This will save the current data without exiting the screen

Cancel: Any changes made to the current screen record or the last time the ‘Save’ Button was pressed will be lost.

Delete: Not applicable to the Detail panel



Contents Tab

Entry and Display fields


Add a record to define an Inquiry.

Inquiry Type: Select a Type from the drop-down list. Options are:

    • ASSEMBLYINQUIRY - Inquiry by selecting Assembly OrderNumber
    • CUSTOMERINQUIRY - Inquiry by selecting Customer
    • DELIVERYINQUIRY - Inquiry by selecting Delivery Number
    • INTERNALINQUIRY - General inquiry not linked to any specific customer/job/assembly/delivery/supplier/product
    • JOBINQUIRY - Inquiry by selecting Job OrderNumber
    • PRODUCTINQUIRY - Inquiry by selecting Itemnumber or DescriptorNumber
    • SUPPLIERINQUIRY - Inquiry by selecting Supplier


Inquiry Name: Enter a name for this inquiry. This will be displayed as the title of this Inquiry.

Inquiry Style: Select the appropriate style from the drop-down list. Options are: 

    • CHART - Only a chart is displayed in this inquiry.
    • CHART-SCORECARD - The inquiry displays both a chart and a scorecard (KPI values)
    • CHART-SCORECARD-LISTING - The inquiry displays a chart, a scorecard, and a listing
    • CHART-LISTING - The inquiry displays a chart and a listing
    • SCORECARD - The inquiry displays a scorecard only
    • SCORECARD-LISTING - The inquiry displays a scorecard and a listing
    • LISTING - The inquiry displays a listing only

Seq No: Enter a sequence number for the inquiry.

Inquiry Explanation: Enter a brief explanation of the Inquiry. This will be displayed under the title when the inquiry is run.

Display Customer Ageing: Tick this Checkbox if Customer Ageing balances are to be included in the inquiry. (Applicable to CUSTOMERINQUIRY / INTERNALINQUIRY). 

If selected, Ageing data will be displayed first before Chart/Scorecard/Listing. 

Display Stock Levels: Tick this Checkbox if Stock Levels are to be displayed in the Inquiry. (Applicable to PRODUCTINQUIRY).

If selected, Stock Levels will be displayed first before Chart/Scorecard/Listing. 



Each record adds an inquiry to the contents of the Pack. You can add in as many records as required.

The following sub-tabs at the bottom section of the screen is used to specify the parameters required to generate the inquiry style. 



Listing sub-tab:

If the inquiry style includes listing, then this tab needs to be filled in for that Inquiry record.


Listing Caption: Enter a descriptive caption for this Inquiry listing. (Example: Open Sales Orders)

Header Line: enter the labels and fieldnames of the header record to be displayed here.

Example: '<b>Order No: </b>' || ordernumber || ' ' || '<b>Order Date: </b>' || (select * from RETURN_DATEASTEXT(orderdate)) 

Explanation: This whole string is made up of : Label1 || Field1 || space || Label2 || Field2

where Label1 ( '<b>Order No: </b>' ) is displayed as Order No: 

Field1 ( ordernumber ) is the value of ORDERNUMBER from SALESHEADER table

Label2 ( '<b>Order Date: </b>' ) is displayed as Order Date: 

Field2 in this example uses a special Procedure (RETURN_DATEASTEXT) to convert the date field (orderdate) into a text field.

Header Note: (Optional) Enter the note fieldname where the header notes is coming from. Use this if you wish to display notes from the table into your listing.

(Example: ordernotes )

Header to Detail Key: Optional. Enter the fieldname of the keyfield that links the detail records to the header record. If you have detail records to display, then use this to link the header and detail records.

(Example: ordernumber )

Header From and Where Clause: Enter the From and Where clause for the SQL statement used to extract the required header records. 

Example: from salesheader where customer = :passedselectedvalue and orderstatus <> 'Closed' order by sysuniqueid 

Explanation: In this example, the :passedselectedvalue is the Customer you selected when initiating this inquiry. 


For other inquiries, the :passedselectedvalue could be the Assembly/Job ordernumber, DeliveryNumber, ItemNumber, DescriptorNumber, or Supplier.

You can also use :passedemployee (which refers to the mobility user name) in the inquiry as well where appropriate.


So in this example, the SQL Select statement for the Header Lines in the Listing becomes:

Select '<b>Order No: </b>' || ordernumber || ' ' || '<b>Order Date: </b>' || (select * from RETURN_DATEASTEXT(orderdate)) 

from salesheader 

where customer = :passedselectedvalue and orderstatus <> 'Closed' 

order by sysuniqueid

Header Results Restriction Count: If you wish to restrict the number of Header records displayed,then enter the number here.

Header Indicator Logic: Optional. Either enter the Colour field name or a CASE statement to define when a certain colour should be displayed. This is used to highlight the header record by displaying the appropriate colour in a box next to the header record.

Example1: trackingcolour

Explanation1: This will simply display whatever the TrackingColour is for that header record.


Example2: case when (orderdate < (current_date - 365)) then 255 else 16777215 end

Explanation2: This case statement highlights those orders whose date is older than one year with a RED (255) colourbox. Others will have a WHITE (16777215) colourbox.


There is an analysis view in Mobility Menu - Views which displays all the available colours and the associated numbers. 

You can use this colour chart to select the colours you wish to use with your case statement. 


Detail Line: Optional. Enter the labels and fieldnames of the detail record to be displayed here.

Example: cast(orderqty as decimal(18,3)) || ' ' || lineunit || ' <b>' || codetype || '</b> ' || linecode || ' : ' || linedescription || ' '

Explanation: This whole string is made up of : Field1 || space || Field2 || space || Field3 || space || Field4 || : || Field5 || space

where Field1 is ORDERQTY cast as decimal(18,3) 

Field2 is LINEUNIT 

Field3 is CODETYPE highlighted in bold 

Field4 is LINECODE 

Field5 is LINEDESCRIPTION

Detail Note: Enter the note fieldname where the detail notes is coming from. Use this if you wish to display notes from the table into your listing.

(Example: linenotes )

Detail From and Where Clause: Enter the From and Where clause for the SQL statement used to extract the required detail records.

Example: from saleslines where ordernumber = :HEADERKEY order by linenumber

Explanation: :HEADERKEY is a reserved word that holds the value of the fieldname indicated in "Header to Detail Key".

In this example, the SQL Select statement for the Detail Lines in the Listing becomes:

Select cast(orderqty as decimal(18,3)) || ' ' || lineunit || ' <b>' || codetype || '</b> ' || linecode || ' : ' || linedescription || ' '

from saleslines 

where ordernumber = :HEADERKEY 

order by linenumber

You can also use the :PassedEmployee parameter in the Detail Query. For example the Header Query could be showing job order information while the Detail Query could show Timesheet entries captured for that same job for the Freeway User.

Detail Queries can run independently of the Header Query. For example, Header Query could display total value of all orders for the month, while the Detail Query could display all individual orders for the month.

Detail Results Restriction Count: If you wish to restrict the number of Detail records displayed, then enter the number here.

Detail Indicator Logic: Optional. Either enter the Tracking code field name or a cast statement to define when a certain colour should be displayed.

(Refer to examples in Header Indicator Logic).



Chart sub-tab:

If the inquiry style includes Chart, then this tab needs to be filled in for that Inquiry record. For Inquiries, only horizontal Bar charts are available.


Chart Caption: Enter a caption for the chart. This will be displayed as the title of the chart. (Example: Sales by Country )

Chart Style: Select one of the options from the drop-down list:

Single Series : Chart is based on the values of one series as defined in Series One Value.

Two Series : Chart is based on the values of two series as defined in Series One and SeriesTwo Value.

Percentage Series : The value is expressed as a percentage of the total values of all selected records. Percentage Series require only the Series One Value and Series One Label to be filled in.

Series One Value: Enter the fieldname or aggregate expression here for the first series or for the Percentage Series.

Example: SUM(INVOICENETTAMOUNT)

Series Two Value: This is applicable only to Two Series charts. Enter the fieldname or aggregate expression here for the second series. 

Example: COUNT(INVOICENUMBER)

Series One Label: Enter the label for the first series or Percentage series. This can be a text string, a fieldname or a case statement.

Example: case when coalesce(billingcountry,'') = '' then 'Not Specified' else BillingCountry end

Series Two Label: This is applicable only to Two Series charts. Enter the label for the seconnd series.This can be a text string, a fieldname or a case statement.

Example: case when coalesce(billingcountry,'') = '' then 'Not Specified' else BillingCountry end

Series One Format: Select one of the drop-down options:(None, Local Currency, FX Currency,Decimal ). This is used to format the values displayed in Series One.

Series Two Format: Select one of the drop-down options:(None, Local Currency, FX Currency,Decimal ). This is used to format the values displayed in Series Two.

Chart From and Where Clause: Enter the From and Where clause for the SQL statement used to extract the required data for this chart.

Example: from salesinvoiceheader where customer = :passedselectedvalue group by 3,4 order by 1 desc

Explanation: The "group by" clause is only used when Series One Value or Series Two Value is an aggregated value.

The number 1 refers to Series One Value; 2 refers to Series Two Value, 3 refers to Series One Label, and 4 refers to Series Two Label.

So for this chart, the SQL Select statement becomes like this:

Select sum(INVOICENETTAMOUNT), count(INVOICENUMBER), 

case when coalesce(billingcountry,'') = '' then 'Not Specified' else BillingCountry end, 

case when coalesce(billingcountry,'') = '' then 'Not Specified' else BillingCountry end 

from salesinvoiceheader 

where customer = :passedselectedvalue 

group by 3,4 

order by 1 desc

Like your typical SQL statement, always group by the numbers which are not aggregate values.

In this example, the :passedselectedvalue is the Customer you selected when initiating this inquiry. 

For other inquiries, the :passedselectedvalue could be the Assembly/Job ordernumber, DeliveryNumber, ItemNumber, DescriptorNumber, or Supplier.

You can also use :passedemployee (which refers to the mobility user name) in the inquiry as well where appropriate.

Chart Results Restriction Count: If you wish to restrict the number of records displayed, then enter the number here.



Scorecard sub-tab:


If the inquiry style includes Scorecard, then this tab and the Scorecard (Statistics) tab needs to be filled in for that Inquiry record.

Scorecard Caption: Enter the caption for the Scorecard here.



Scorecard (Statistics) sub-tab:

You can have multiple statistics displayed in the Scorecard.


Seq No: Enter the sequence number of the statistic here.

Statistic Name: Enter the name of the statistic here.

Statistic Value Field: Enter the fieldname or aggregate expression here.

Example: sum(INVOICENETTAMOUNT) 

Statistic From and Where Clause: Enter the From and Where clause for the statistic's sql statement here.

Example: from salesinvoiceheader where customer = :passedselectedvalue 

Explanation: In this example, the :passedselectedvalue is the Customer you selected when initiating this inquiry. 

For other inquiries, the :passedselectedvalue could be the Assembly/Job ordernumber, DeliveryNumber, ItemNumber, DescriptorNumber, or Supplier.

You can also use :passedemployee (which refers to the mobility user name) in the sql as well where appropriate.

Statistic Format: Select the appropriate option for this statistic from the drop-down list. Options are:

    • Date
    • Decimal
    • Integer
    • Local Currency
    • Local Currency No Decimals
    • Text

Highlight Conditional Type: Select the Highlight Conditional Type and the corresponding Conditional Value if you wish to highlight each statistic based on certain conditions. Options are:

    • No Highlight
    • Equal To the Conditional value
    • Greater Than the Conditional value
    • Less Than the Conditional value

Highlight Conditional Value: Enter the Conditional value for the corresponding Highlight Conditional Type. This is a decimal value.

Highlight Colour: You can select one of four colours to be your highlight colour for the statistic. (Choices are Blue, Green, Yellow, Red.)

Group Seq: Enter the Group Sequence here to order the group of statistic

Group Name: Select the Statistic Group from the drop-down list. (Statistic Groups are defined in Mobility Settings.)

Using the Group Seq and Group Name will enable you to group various statistics together and present the statistics in the desired order.

You can click the Add button to add another statistic.



Buttons

Close: This will close the screen. If you have any unsaved data then you will be asked if you wish to save it before the screen is closed.

Add: This will bring up a blank screen for entry of another record.

Save: This will save the current data without exiting the screen

Cancel: Any changes made to the current screen record or the last time the ‘Save’ Button was pressed will be lost.

Delete: Not applicable to this panel

Display: This button allows you to display the Inquiries on your Ostendo screen (to preview it).

You will be prompted to select the Employee name and the record to run this inquiry against (Customer / Supplier / Job / Delivery / Assembly / Product).

The Employee you select must have this Inquiry pack name assigned in his/her Employee Mobility Settings - Device tab.