Table source record list picked from several queries

oscar cordeiro

Question

Hello,

I have a table where I do store sales. For those records I have a "open date" and "close date" ... pending sales for me are those where the "close date" is not filled yet.

1 - My main doubt ... I ended up creating 3 queries on the "preparation" of my web screen where only one of them is picked. I used a "swich" (together with a variable (AFT) with the content = "Open", "Close" or "All") ... The first Query does have CloseDate = NullDate(), the second does have CloseDate <> NullDate() and the third, no dates criteria.

Was it necessary to create those 3 queries or I just used an the unecessary solution? ... if so, can you please show me the other way?

2 - Based on point 1, I had to pick the right query for the table source record list ... again, I ended up with a solution whereas I do have doubts if there was no other way.
source record list> If(AFT="OPEN",OPEN.List,If(AFT="CLOSED",CLOSED.List,ALL.List))

Is it a common procedure (or solution) to use an expression(if) to pick a source record list?

Thank you so much,

OC

28 Jan 2011

MVP

I'm not sure if I understand your question correctly.

What is it what you want to see on the screen?

Is it a tablelist with a filter on "open/closed" ?
My first thought is you don't need 3 queries :)

Outsystems provide many option to get what you want in 1 query.
for example expand-inline in the advanced query is such a thing.

On your last question... it depends. :)

28 Jan 2011

Paulo Tavares

Staff

Hi Oscar,

Indeed, my first thought went to Joost's answer: you probably do not need 3 queries, even if you may have to refactor that condition somehow.

Still, if you were to use 3 queries, my suggestion would be for you to:

1 - Create a local variable of Record List data type, and with the same record definition as the queries;
2 - After each query (in each branch) assign the query's record list result to that local variable;
3 - Use that local variable in the Table Records.

Does this help?

Let us know how it goes.

Regards,

Paulo Tavares

28 Jan 2011

oscar cordeiro

Good night Joost and Paulo,

I do reply to the last post but my thank you to both of you.

Indeed I had present (or at least, I suspected) that there was no need for 3 queries just because I was not able to parse a variable inside a single query ... but the skills were missing. :)

I am sure you guys already figured out that I am not a professional developer but Outsystems had been for me quite a challenge over the past fee days and, although I do not intend to became a professional on this, I have decided to give it a try on transposing to this wonderful environment some of the small personal databases that I am using locally (let's see how I manage with this one first)...

Concerning my yesterday problem, I managed to solve it with only one query using the "advanced query" (as you both kindly suggested). I changed the content of the variable to "='1900-01-01'" (for OPEN sales), "<>'1900-01-01'" (for CLOSED sales) and (>='1900-01-01'") for ALL.

Insided the "advanced query" I've created the parameter named "parmFiltraData" with the "expanded inline" property = yes and feed that parameter with my variable (adding a "new argument" for the query properties")

I droped the CONTACTOS entity on the Output Structure of my "advanced query" and wrote on the SQL:

SELECT {Sales}.* FROM {Sales} WHERE {Sales}.[Data_Fim]@parmFiltraData

Trough this process, I had the chance to figure out the purpose of the "Test Inputs" tab and the relation between that tab and the "Test Output" for results ... simply great! ... for a final conclusion of all the tries and errors (attempting on a final solution), I've got the idea that I cannot use the NullDate() as part of the parameter ... can you please confirm if I could and did not use the correct procedures?

Once again, thank you so much for the great support and patience!

Kind regards,

OC

29 Jan 2011

Paulo Tavares

Staff

Hi Oscar,

I'm glad that you managed to sort it our - and mind you, I wasn't necessarily going for the Advanced Query suggestion, since I wasn't sure how comfortable you were with SQL syntax, but you managed to create a very elegant solution there!

However, I would expect you would be able to use NullDate() in your expression. Could it be that you forgot to put the quotes around NullDate() ?

For example, the

"<>'1900-01-01'"

condition should be:

"<>'" + NullDate() + "'" .

Have you tried it? Did it not work?

Regards,

Paulo Tavares

31 Jan 2011

oscar cordeiro

Hello Paulo,

Thank you for the reply. I will be able to test your indications only today by noon but, I did something almos like that (though expressions wouldn't need the ' )

I expected to concatenate the <> with the NullDate()

"<>'" + NullDate()

I'll give it a try by noon.

Thank you so much.

Óscar Cordeiro

31 Jan 2011

Paulo Tavares

Staff

Hi Oscar,

Well, yes, you would be right.

However, if the exact SQL condition is

<>'1900-01-01'

for instance, then, if you do

"<>" + NullDate()

what you'll get is

<>1900-01-01

without quotes ( ' ).

If what you do is

"<>'" + NullDate()

what you'll get is

<>'1900-01-01

what you'll get is

<>'1900-01-01

and you're still missing the ' on the right side.

That's why you would need

"<>'" + NullDate() + "'"

so that the result would be

<>'1900-01-01'

which is what you wanted in the first place :)

I hope this makes it clear. Let me know if this works.

Regards,

Paulo Tavares

31 Jan 2011

oscar cordeiro

Hello Paulo,

I've tried and got : "Error: 'ParmFilterData' Test Value must be a Text Literal Value".

I guess that the error reason is somehow related with the fact of the parameter data type is Text (instead of date) but , I cannot use the "expand inline" if the parameter is any other data type then text... it seems to to be the only data type for "expand inline".

All the other tries I've done led me to the same dead end ... I even tried something like ... "<>'" + DateToText(NullDate()) + "'"

I believe that It can be used, I'll try several other combinations.

Thank you so much.

OC

31 Jan 2011

Paulo Tavares

Staff

Hi Oscar,

While that error would happen for the test value - since, indeed, Testing the query it expects to receive an actual value :) - in runtime I would expect that the last solution you posted would work.

Weren't you able to publish it and test it?

Regards,

Paulo Tavares

01 Feb 2011

oscar cordeiro

Hello Paulo,

You are right, it works like a charm! I've taken for sure the "Test Input" result at the time and didn't go ahead.

Just a final doubt for this part (to make sure if I solved the problem the right way):

One of the fields from my table (entity) receives the data trough a combo box on another screen (new/edit).
Now, with the advanced query, it shows the ID (number) instead of the supplier name... as on msaccess, relationship between tables stores the primary key

I was using something like: SELECT {Contactos}.* FROM {Contactos} and the query output was (record ID, Action description, Supplier):

10 - José apresentou catalogo - Pastelaria ACME

Now, with the advanced query, the output was:

10 - José apresentou catalogo - 12
(where 12 is the ID from my supplier "Pastelaria ACME" on suppliers entity)

How I solved (please let me know if is acceptable):

I've changed the SQL to a LEFT JOIN query:

SELECT {Contactos}.[Id],{Contactos}.[Descricao],{Entidades}.[Nome]
FROM {Contactos}
LEFT JOIN {Entidades}
ON {Contactos}.[Entidade]={Entidades}.[Id] WHERE {Contactos}.[Data_Fim]@parmFiltraData

It works perfectly but could I do it other way (more simple or technically more recomendable)?

Thank you so much!

Óscar Cordeiro

01 Feb 2011

Paulo Tavares

Staff

Hi Oscar,

Well, yes, that's a great way to do it!

Regarding the why it was working previously, it would boil down to your specific data model, and the data types of the attributes. I wouldn't know without having looked into it.

However, the way you did it - with the JOIN - would be the way I would do it as well.

Regards, and great job! Do let us know how everything goes :)

Paulo Tavares

02 Feb 2011

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.

See the full guidelines