Tanakorn Kittipiwatkul
Rank: #8286
21Views
12Comments
Solved
How to insert data from SQL into dummy table?
Question
Traditional Web
Application Type
Traditional Web

Hello, I have a question.I want to create a histroy table that show time in and time out of user. I've already try simple method to build the table from Nikhil Gaur in this post Link and it's work fine but I have another problem. I want to put my query from SQL into the table to look like this

from the picture if there is a record in my query from SQL it will show at the same day as it's created but if it doesn't it will show "-" instead

But right now I got the table that look like this

this is the property of my table

this is GetDates Action

this is inside of my preparation

and this is record from SQL that I want to show in the table

I have already try many methods but It doesn't work for me so can anyone please help me to resolve this issue?

or maybe just make an example so I can get the idea.

EARS.oap

0
0
on 30th Jan
Copy link to comment
Tom Zhao
Rank: #288
Solution

Replying to Tanakorn Kittipiwatkul's comment on 31 Jan 2021 10:11:26

Hi Tanakorn

Please refer to the OML file I attached.

Kind Regard,


See solution in context
EARSWEB.oml

0
0
on 31th Jan
Copy link to comment
João Marques
Rank: #56

Hi Tanakorn,


You have done the most difficult :)

The action GetDates, which you are using to populate your list, is a list of Dates, while it should be a list of TimeStampHistory record so you can have the details needed to populate the table, so it should be like this:

After that, then you can populate the fields of your table using the output of the GetDates action:


EARS_Web module OML in attachment with the changes.


Regards,
João

EARSWEB.oml

0
0
on 30th Jan
Copy link to comment

Hello, João Marques

Thank you for your help.

but the result of the table is not correct. it's only create the top record of the list

and I'm sorry if I didn't explain the question clearly

The Date column in the table it's not a createdate of timestamp from the record in SQL, but it's the date  range that I want to show to user. I have done this method before and I still don't know how to fix it.

Anyway I'm truly appreciate that you try to help me

Thank you :)

0
0
on 30th Jan
Copy link to comment

Hi Tanakorn,

Inside the Getdates() action, you need to use first a For Each node prior to the If condition. I think that is why, it's only showing the first record.

Hope this helps,

Thank you and kind regards,

Chris

0
0
on 30th Jan
Copy link to comment

Hi, Christopher Bautista 

Thank you for your advice. Is it the same like Tom Zhao explanation?

I don't know if I understand correctly that both of you have suggested

but Thank again for your help.


0
0
on 31th Jan
Copy link to comment
Tom Zhao
Rank: #288

Hi Tanakorn,

Please refer to the image attached.  You have to iterate the results of Getdates() action,

And use ListFilter() to determine if the record of the date exists or not.

Also,  Add a local variable, and use that for datasource.


Kind Regards

ActionFlowjpg.jpg

0
0
on 31th Jan
Copy link to comment

Hi, Tom Zhao 

Thank you for your help. I have tried your method and do what you said exactly like in the image but result still be same. I don't know what I did wrong.


0
0
on 31th Jan
Copy link to comment
Tom Zhao
Rank: #288

Hi 

By the way, You said you are using the method  Nikhil Gaur tells in this post Link.

If you want to be done all by SQL, Maybe you can try Rajat Agrawal' method instead, and left join to your Sql

Kind Regards


0
0
on 31th Jan
Copy link to comment

Hello, Tom Zhao

I have tried SQL method but I think that might be difficult for me so I have tried the simple way as  Nikhil Gaur  

Thank you for your advice.

0
0
on 31th Jan
Copy link to comment
Tom Zhao
Rank: #288
Solution

Replying to Tanakorn Kittipiwatkul's comment on 31 Jan 2021 10:11:26

Hi Tanakorn

Please refer to the OML file I attached.

Kind Regard,


EARSWEB.oml

0
0
on 31th Jan
Copy link to comment

Replying to Tom Zhao's comment on 31 Jan 2021 14:05:46

Hi, Tom Zhao

At first I thought your solution must be done after João Marques's method.

Sorry for my misunderstand and Thank you again for your help and kindness.

0
0
on 31th Jan
Copy link to comment

Once again thank you everyone for helping me.

Best regards

TK.

0
0
on 31th Jan
Copy link to comment

Hi to anyone who has similar problem like my problem and come to read this article.

After Tom Zhao help me to resolve my issue, I have another problem about how to refresh the table with local variable as data source. Here in this link Solution is a solution. I post this because it relate to my issue and I want to share how to solve it.

Best regards

TK.

0
0
on 31th Jan
Copy link to comment