Columns in Table only show a part of odata values

[Former Member]

Dear community,

I have a problem with my Fiori like Master-Detail App.

In my Detail View I have a Table for Lintitems.

<Table

            headerText="{i18n>LineItemTableHeader}"

            items="{project_contracts/?$expand=contract,working_hour}" >

            <columns>

....

<ColumnListItem

                type="Navigation"

                press="handleLineItemPress" >

                <cells>

                    <ObjectIdentifier

                        title="{working_hour/LST_DATE}" />

                    <Text

                        text="{working_hour/DESCRIPTION}"/>

                    <Text

                        text="{contract/CONTRACT_NAME}"/>

                    <Text

                        text="{working_hour/RE_STUNDEN}"/>

                    <Text

                        text="{working_hour/CONSULTANT_ID}"/>

                </cells>

            </ColumnListItem>

If I remove ",working_hour" in binding it shows the contract Line perfectly and add three records to the table this is right.

But addiontially I want some working_hour information so I expand this table. But then the table is empty.

But if I use the url manually on odata server he gaves me the data.

... Pato.svc/project_contracts?$expand=contract,working_hour&$format=json

"results": [

            {

                "__metadata": {

                    "uri": "http://localhost:81/odata/Pato.svc/project_contracts(PROJECT_CONTRACT_ID=1)", "type": "Pato.project_contract"

                }, "contract": {

                    "__metadata": {

                        "uri": "http://localhost:81/odata/Pato.svc/contracts(CONTRACT_ID=1)", "type": "Pato.contract"

                    }, "customer": {

                        "__deferred": {

                            "uri": "contracts(CONTRACT_ID=1)/customer"

                        }

                    }, "project_contracts": {

                        "__deferred": {

                            "uri": "contracts(CONTRACT_ID=1)/project_contracts"

                        }

                    }, "CONTRACT_ID": 1, "CONTRACT_NAME": "DSG_MH", "CHARGE": 120, "BEGIN_DATE": "null", "END_DATE": "null", "CUSTOMER_ID": 1

                }, "project": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=1)/project"

                    }

                }, "working_hour": {

                    "__metadata": {

                        "uri": "http://localhost:81/odata/Pato.svc/working_hours(ITEM_ID=1)", "type": "Pato.working_hour"

                    }, "project_contracts": {

                        "__deferred": {

                            "uri": "working_hours(ITEM_ID=1)/project_contracts"

                        }

                    }, "employee": {

                        "__deferred": {

                            "uri": "working_hours(ITEM_ID=1)/employee"

                        }

                    }, "ITEM_ID": 1, "LST_DATE": "\/Date(1468368000)\/", "DESCRIPTION": "DESCRIP", "TODO": "todo", "INVOICE": false, "RE_DATE": "null", "RE_STUNDEN": 8, "RE_TEXT": "text", "NOTICE": "notice", "COMMITTED": false, "CONSULTANT_ID": 1

                }, "PROJECT_CONTRACT_ID": 1, "WBS_ELEM": 1, "CONTRACT_ID": 1, "ITEM_ID": 1

            }, {

                "__metadata": {

                    "uri": "http://localhost:81/odata/Pato.svc/project_contracts(PROJECT_CONTRACT_ID=2)", "type": "Pato.project_contract"

                }, "contract": {

                    "__metadata": {

                        "uri": "http://localhost:81/odata/Pato.svc/contracts(CONTRACT_ID=1)", "type": "Pato.contract"

                    }, "customer": {

                        "__deferred": {

                            "uri": "contracts(CONTRACT_ID=1)/customer"

                        }

                    }, "project_contracts": {

                        "__deferred": {

                            "uri": "contracts(CONTRACT_ID=1)/project_contracts"

                        }

                    }, "CONTRACT_ID": 1, "CONTRACT_NAME": "DSG_MH", "CHARGE": 120, "BEGIN_DATE": "null", "END_DATE": "null", "CUSTOMER_ID": 1

                }, "project": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=2)/project"

                    }

                }, "working_hour": {

                    "__metadata": {

                        "uri": "http://localhost:81/odata/Pato.svc/working_hours(ITEM_ID=2)", "type": "Pato.working_hour"

                    }, "project_contracts": {

                        "__deferred": {

                            "uri": "working_hours(ITEM_ID=2)/project_contracts"

                        }

                    }, "employee": {

                        "__deferred": {

                            "uri": "working_hours(ITEM_ID=2)/employee"

                        }

                    }, "ITEM_ID": 2, "LST_DATE": "\/Date(1468368000)\/", "DESCRIPTION": "DESCRIP", "TODO": "todo", "INVOICE": false, "RE_DATE": "null", "RE_STUNDEN": 8, "RE_TEXT": "text", "NOTICE": "notice", "COMMITTED": false, "CONSULTANT_ID": 1

                }, "PROJECT_CONTRACT_ID": 2, "WBS_ELEM": 2, "CONTRACT_ID": 1, "ITEM_ID": 2

            }, {

                "__metadata": {

                    "uri": "http://localhost:81/odata/Pato.svc/project_contracts(PROJECT_CONTRACT_ID=3)", "type": "Pato.project_contract"

                }, "contract": {

                    "__metadata": {

                        "uri": "http://localhost:81/odata/Pato.svc/contracts(CONTRACT_ID=2)", "type": "Pato.contract"

                    }, "customer": {

                        "__deferred": {

                            "uri": "contracts(CONTRACT_ID=2)/customer"

                        }

                    }, "project_contracts": {

                        "__deferred": {

                            "uri": "contracts(CONTRACT_ID=2)/project_contracts"

                        }

                    }, "CONTRACT_ID": 2, "CONTRACT_NAME": "DSG_MG", "CHARGE": 100, "BEGIN_DATE": "null", "END_DATE": "null", "CUSTOMER_ID": 1

                }, "project": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=3)/project"

                    }

                }, "working_hour": {

                    "__metadata": {

                        "uri": "http://localhost:81/odata/Pato.svc/working_hours(ITEM_ID=3)", "type": "Pato.working_hour"

                    }, "project_contracts": {

                        "__deferred": {

                            "uri": "working_hours(ITEM_ID=3)/project_contracts"

                        }

                    }, "employee": {

                        "__deferred": {

                            "uri": "working_hours(ITEM_ID=3)/employee"

                        }

                    }, "ITEM_ID": 3, "LST_DATE": "\/Date(1468368000)\/", "DESCRIPTION": "DESCRIP", "TODO": "todo", "INVOICE": false, "RE_DATE": "null", "RE_STUNDEN": 8, "RE_TEXT": "text", "NOTICE": "notice", "COMMITTED": false, "CONSULTANT_ID": 1

                }, "PROJECT_CONTRACT_ID": 3, "WBS_ELEM": 1, "CONTRACT_ID": 2, "ITEM_ID": 3

            }

WBS_ELEM is the id of the current Master record.

So where is my mistake?

I do it on the same way with another table and similiar table structure.

Please help,

Marcel Gatermann

[Former Member]

My syntax should be correct because it works for other tables.

In the log is the following error message:

...The following problem occurred: Invalid date/time value200,OK...

I think the date part in this table is the problem.

This value is in the database: 2016-07-13

It is an mysql database and I use phpodataproducer to create an odata service.

How should I save the date?

[Former Member]

Yes the Date attribute was the error.

I changed it into int.

[D_Chia]

out of curiosity, when you said "I changed it into int" ... does it mean you changed to a different field to display, changed the data type of the db column or you did a convertion (eg. thru formatting function) from '/Date(nnnnnnnn)/' to seconds ?

actually, i would have expected SAPUI5 (which is using datajs) to have converted the date value from the JSON form to JS Date automatically when using OData model ...

perhaps it does not have built-in parsing for the format "\/Date(1468368000)\/" (yet).  i tested a manual formatter function and it works so i'm just interested if you have some other way of doing it.

[Former Member]

I changed the database to int and have to convert it manually.

I don't know why mysql date ist not compatible with sapui5 date

[D_Chia]

i have tested with a NW Gateway OData service which will supply Date.Time type column values in the form

"/Date(1243436000)/"

when it is read into SAPUI5's OData model it is automatically converted to JS Date (object) form correctly.

the view output will be in the format below:-

we would then likely want/need to format it into something more readable like DD/MM/YYYY, etc (using Formatter function)

so, back to your case i guess it's the OData model parsing algorithm not recognizing the slightly more obscure format ("\/Date(xxxxxxxx)\/") as convertible to JS Date.

perhaps if there's a way to configure or influence such date values output by the service generated by odataphpproducer ... but i guess that's for a different day

thanks for your time in this discussion

[D_Chia]

i have not used the form of aggregation binding you mentioned

items="{project_contracts/?$expand=contract,working_hour}"

but i have used the format similar to the code below to get and display expanded elements from OData

<Table

      id="table"

      items="{

          path: '/ProductSet',

          parameters: {

              expand: 'ToSupplier'

          },

          sorter: {

              path: 'ProductID',

              descending: false

          }

      }"

     noDataText="{worklistView>/tableNoDataText}"

     updateFinished="onUpdateFinished">

[Former Member]

On every place where I try to use data of working_hours it results in no data.

No one any idea?

Best regards,

Marcel

[Former Member]

Even if I try to bin /working_hours to the Master Page there is no result.

What is wrong with the table working_hour. The normal odata Service is working, only in my App it can not be consumed.

Best regards,

Marcel

[Former Member]

Here it will be shown in a simpler way.

Pato.svc/project_contracts?expand=working_hour&$filter=WBS_ELEM eq 1&$format=json

{

    "d" : {

        "results": [

            {

                "__metadata": {

                    "uri": "http://localhost:81/odata/Pato.svc/project_contracts(PROJECT_CONTRACT_ID=1)", "type": "Pato.project_contract"

                }, "contract": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=1)/contract"

                    }

                }, "project": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=1)/project"

                    }

                }, "working_hour": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=1)/working_hour"

                    }

                }, "PROJECT_CONTRACT_ID": 1, "WBS_ELEM": 1, "CONTRACT_ID": 1, "ITEM_ID": 1

            }, {

                "__metadata": {

                    "uri": "http://localhost:81/odata/Pato.svc/project_contracts(PROJECT_CONTRACT_ID=3)", "type": "Pato.project_contract"

                }, "contract": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=3)/contract"

                    }

                }, "project": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=3)/project"

                    }

                }, "working_hour": {

                    "__deferred": {

                        "uri": "project_contracts(PROJECT_CONTRACT_ID=3)/working_hour"

                    }

                }, "PROJECT_CONTRACT_ID": 3, "WBS_ELEM": 1, "CONTRACT_ID": 1, "ITEM_ID": 3

            }

        ]

    }

}

So these two records should be shown, if I bind project_contracts/?expand=working_hour to my table it is empty, but why?

Best regards,

Marcel