Presenting Data from DB

 Hello everyone,

I have a problem with presenting data from DB. There is short introdcution:

In SQL DB SERVER i have a table called  email request and there are rows called id, request number, sender, title, recieved at, email body and folder id. This is table stores data about emails. The problem is that you can have many mails from one person that are related to one request number and i would like to present them in one grid - all of them. 

I've created a expression rule to get request mail by request Number

 a!queryEntity(
entity: cons!SAMPLEDATASTOREENTITY,
query: a!query(
filter:
if(
rule!APN_isBlank(ri!requestNumber),
null,
a!queryFilter(
field: "requestNumber",
operator:"=",
value: ri!requestNumber
)
),
pagingInfo: a!pagingInfo(
startIndex: 1,
batchSize: -1
)
)
).data

But the problem is when i would like to present this on my interface via TEST(for eg. rule!AA_getRequestMalByRequestNumber("SOME REQUEST NUMBER123")and this is presenting me just first row with this number 

Can somebody help me with this present all of them in this grid

Thank you in advance Dawid

  Discussion posts and replies are publicly visible

  • Test your query to make sure it is returning multiple rows. If it is, check your input to your expression to make sure it is configured to accept an array as the input. Please post the code for your grid and or interface for displaying the data.
  • 1) Check the database whether you have multiple entries for that request id
    2) If DB has multiple rows, Check the pagination of the query since the query is returning value
    3) If the query is returning multiple, then check the variable that you have passed whether it is multiple type or single type

    Thanks,
    Logu
  • a!gridField(
    label: "mailing with branch",
    labelPosition: "ABOVE",
    totalCount: 5,
    columns: {
    a!gridTextColumn(
    label: "Sender",
    data: {ri!request.updatedBy, ri!requestemail.sender}
    ),
    a!gridTextColumn(
    label: "Date",
    data: {ri!request.updatedOn, ri!requestemail.receivedAt}
    ),
    a!gridTextColumn(
    label: "Message Content",
    data: {"Reasons for referral for improvement: "&
    ri!request.errorType&"."&
    ri!request.errorType2 &
    ri!request.errorType3&
    ri!request.errorType4&
    ri!request.errorType5&" "&
    "Comment: "&
    ri!request.errorDetails,
    rule!AA_getRequestemailbyRequestNumber("AA-B-181002-557")} --------------/* WHEN I TYPE THIS NUMBER IT RETURNS BOTH EMAIL BODIES BUT IT RETURNS ENTIRE ROWS LIKE ID REQUEST NUMBER SENDER ETC HOW CAN I GET RID OF THIS EXCEPT EMAIL BODY */
    )
    },
    value: a!pagingInfo(startIndex: 1, batchSize: 10),
    saveInto: {},
    showwhen: or(ri!request.requestStatus=5, ri!request.requestStatus=10),
    disabled: false,
    validations: {},
    shadeAlternateRows: true
    )

  • For just returning the email body, you can use the index() function to pull it out.

    Example: 

    =load(
      local!emailCdt: rule!AA_getRequestemailbyRequestNumber("AA-B-181002-557"),
      local!emailBody: index(
        local!emailCdt,
        "emailBody", /* replace with the correct field name */
        {}
      ),
      a!textField(value: local!emailBody)
    )

    If you only want the email body whenever you use this query, you can further optimize by adding a selection to your query and selecting only the body text column you want returned.

  • It gave me below resulte:

    [@attributes=[@anyAttribute=[validationGroups:,requiredMessage:,_cId:6aba5f15b6e2bb4676dbfc5e0fdb47af], @nil=], label=, instructions=, required=, readOnly=, disabled=, placeholder=, value=done

    ; Poprawione

    , validations=, saveInto=<<jA0EAwMCtBWatGjy+sIB0sB1Ad3r5dMThoeAbCFLzPCW3FqgJ3S70wpDF8dfJwzAUTf/i4lz08n5

    +G/DNFllDtPZbVIZbI/eoHIKXdwusnZwDcaRYBXEC54rwYDD90Qbvd342/PfXPz5qPia8rkHeq4D

    7DmSW02QCI++AD/RdT4cLNYd/6iVCMLH9lsmpm9b8EXpKCCQSydgLeEFPI+YumnU8/YP/q3jkgIL

    Hur6pjIeGAtqY42DdHIrE0WG2tbi3/beODQXE7NOXrb7zFwuoYTcqW2Qi6VOEaAs6GYt6MBWQroq

    BxWWKjgxOiVBVwvCRSPaQAekpXo8pDIj/e5c+LuSttsNGPETw20gF6hQ1qOCyO2zeGXh9lmkD4Z7

    zKhExvCiL42sAklggFhKHMK9ZTJJinknsxES1yzmDulDERS1OT4N36oU

    >>, refreshAfter=UNFOCUS, keyboard=STANDARD, labelPosition=ABOVE, align=DEFAULT, actions=, helpTooltip=, masked=]

     

  • Hi dawids0001,

    From your screenshot, it looks like you are trying to display a form component in that grid column, instead of a text value.

    Are you expecting to see value "done; Poprawione" here? If so, check to see if you have that value wrapped in a component somewhere (for example, in my above post I used a text field as an example, but you would just want to put the value of local!emailBody into the grid text column instead of the text field).

    If that is not the value you are expecting, can you post the code you are using to generate this grid?
  • You do not need the query ("AA_getRequestemailbyRequestNumber") in the a!gridTextColumn unless it is querying data from another table. To access a specific field in that query you could try rule!AA_getRequestemailbyRequestNumber("AA-B-181002-557).thatSpecificField (or use the index function as described by Mike to handle situations with null values). Although, I suspect the field you want is already in the ri!request CDT.
  • It works but id like to don't have this specific number inside my brachet. There is one number for everysingle request so i tried this rule!AA_getRequestemailbyRequestNumber(ri!request.requestNumber).emailBody as a test inside interface it works pretty good but when i've created a test case from action there is an error "Expression evaluation error in rule 'aa_f_backofficeemployeetask' at function a!gridTextColumn [line 108]: Invalid index: Cannot index property 'responder' of type Text into type List of Variant" so then i tried change it for this rule!AA_getRequestemailbyRequestNumber(ri!RequestemailbyRequestNumber.requestNumber).emailBody and there was a problem with batchsize and totall count so i change it works on test in action but it returns entire collumn emailbody from my table and there is completely mess. Can somebody help me get through it. Thank you very much :)
  • Without seeing your data structures, i'm guessing a little...try this...if this works, we can make it pretty later...
    load(
    local!pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: 10
    ),
    with(
    local!emailData: if(
    rule!APN_isBlank(
    ri!requestNumber
    ),
    null,
    a!queryEntity(
    entity: cons!SAMPLEDATASTOREENTITY,
    query: a!query(
    filter: a!queryFilter(
    field: "requestNumber",
    operator: "=",
    value: ri!requestNumber
    ),
    pagingInfo: a!pagingInfo(
    startIndex: 1,
    batchSize: - 1
    )
    )
    )
    ),
    if(
    or(
    rule!APN_isBlank(
    ri!requestNumber
    ),
    local!emailData.totalCount = 0
    ),
    {},
    a!gridField(
    label: "mailing with branch",
    labelPosition: "ABOVE",
    totalCount: local!emailData.data.totalCount,
    columns: {
    a!gridTextColumn(
    label: "Sender",
    data: {
    local!emailData.data.sender
    }
    ),
    a!gridTextColumn(
    label: "Updated By",
    data: {
    local!emailData.data.updatedBy
    }
    ),
    a!gridTextColumn(
    label: "Updated On",
    data: {
    local!emailData.data.updatedOn
    }
    ),
    a!gridTextColumn(
    label: "Date Received",
    data: {
    local!emailData.data.receivedAt
    }
    ),
    a!gridTextColumn(
    label: "Error Type",
    data: {
    local!emailData.data.errorType
    }
    ),
    a!gridTextColumn(
    label: "Error Type 2",
    data: {
    local!emailData.data.errorType2
    }
    ),
    a!gridTextColumn(
    label: "Error Type 3",
    data: {
    local!emailData.data.errorType3
    }
    ),
    a!gridTextColumn(
    label: "Error Type 4",
    data: {
    local!emailData.data.errorType4
    }
    ),
    a!gridTextColumn(
    label: "Error Type 5",
    data: {
    local!emailData.data.errorType5
    }
    ),
    a!gridTextColumn(
    label: "Error Details",
    data: {
    local!emailData.data.errorDetails
    }
    ),
    a!gridTextColumn(
    label: "Email Body",
    data: {
    local!emailData.data.emailBody
    }
    )
    },
    value: local!pagingInfo,
    saveInto: local!pagingInfo,
    disabled: false,
    validations: {},
    shadeAlternateRows: true
    )
    )
    )
    )