SearchKeyWord

  
Hello 

How can i Make Search and display records from My StuctureTable knowing that i have Table Record on my Screen contain OutPutStructure,so i try to make the same Search method as Search from the normal Tables but it's not working,if there is a way to resolve this issus i'll be happy so please help
cheers and best regards

Hi Ilham,

I'm afraid your question isn't all too clear. What are you trying to achieve? In general, you would feed the search field input to a refresh query after the user presses the Search button (Ajax Submit), then redraw the table.
Hi Kilian

what i'm trying to achieve is to know how can i make a search from the Structure Table.have you see this attahement please

Thank you
This is my Preparation Logic see this attachement please
This is my RefreshAction,i hope that you understand my issus please take a look at this new attachement

cheers
Ilham,

You will have to use the variable bound to the search input you have on the screen as input to the SQL node. Contrary to aggregates SQL nodes don't have access to the variables in scope so you have to defined input parameters in the SQL node to pass these variables into the SQL node and use them there.

It also appears to me that you wouldn't need to use a SQL node for this one. Can you shows us the statement in the SQL node?

Cheers
Hi Vieira
i don't understand how can i defiend and how can i use the input parametres as you explain here if you make me exemple please
so take look at the statement please :
SELECT {Engins}.[Num_Ordre],{Engins}.[Marque],{Engins}.[Cout_Horaire],{Entretien_Voiture_Camion}.[Kilometrage_heur],{Entretien_Voiture_Camion}.[Date_Kilometrage], SUM({Entretien_Item_VC}.[Prix_Total])
FROM
{Entretien_Item_VC}
inner join {Entretien_Voiture_Camion} on {Entretien_Voiture_Camion}.[Id] = {Entretien_Item_VC}.[EntretienID]
inner join {Engins} on {Engins}.[Id] = {Entretien_Voiture_Camion}.[EnginID]
 

GROUP BY   
{Engins}.[Num_Ordre], {Engins}.[Marque],{Engins}.[Cout_Horaire],{Entretien_Voiture_Camion}.[Kilometrage_heur],{Entretien_Voiture_Camion}.[Date_Kilometrage]
ORDER BY
SUM({Entretien_Item_VC}.[Prix_Total] ) DESC
Hi Ilham,

First of all, judging that query you do not need an advanced query, but can use an aggregate. This will make your life much easier, as an aggregate can use all available variables in scope. That said, if you want to keep the advanced query, add a parameter to it and use that parameter in the query (presumably in the WHERE clause).
How can i bound the input SerchKeyWorld to my input parameters on the query? please can you give some exemples please
cheers

Ilham,

Since these are rather fundamental questions, I suggest you take a look at the tutorials. I'd be glad to help anyone stuck, but being stuck at such an early time, you'd better off investigating about the platform more.
André Vieira wrote:
Ilham,

You will have to use the variable bound to the search input you have on the screen as input to the SQL node. Contrary to aggregates SQL nodes don't have access to the variables in scope so you have to defined input parameters in the SQL node to pass these variables into the SQL node and use them there.

It also appears to me that you wouldn't need to use a SQL node for this one. Can you shows us the statement in the SQL node?

Cheers
 Hi Vieira

i'm trying to create the inputs parameters in my advanced query but i couldn't, so realy i don't know how can i bind the input SearchKeyworld to these inputs parameters 
Please give me example 
cheers
 
Hi Ilham,

Creating input parameters to an advanced query is very basic. Did you take a look at the tutorials like I suggested?
Hi

I created input parameter  ' SearchInput ' and i created this where Clause but when i'm testing this query i had en error Incorect syntax near ' |'  :

WHERE ((@SearchInput = '')  or ({Engins}.[Marque] Like '%' || @SearchInput || '%'))
Ilham,

I'm not sure why you think that "||" is valid SQL syntax?
Hi
When i test this query it's done but when i publish my Version and trying to test it on the screen it's didn't work??!!
i think there are some things missing!!?

 WHERE ((@SearchInput = '') or ({Engins}.[Marque] Like @SearchInput))

Ilham,

In general you'll want something like

LIKE '%' + @SearchInput + '%'
so that you'll get all records in case the search input is empty. Is that what you're missing? "it didn't work" isn't enough for me to determine what exact problem you're experiencing.
So what i want now is to make search on my sceen as you'll see at this attachement
Ilham,

Can you attach the oml? It'll be easier to check what's wrong than via PNGs.
So Take a look at Facturation_Entretien Screen please 
cheers
Hi 
Also, what i want exactly is to make search between Two inputs parameters for example : i want to display just the Facturations records between two date 03/05/2015 and 03/06/2015
Hi Ilham,

You're on the right track, the query works. The problem lies in the refresh: you refresh the filter instead of the table, so the refreshed data is never displayed. I've attached an OML that should fix this. Also, in the preparation, I've changed the Advanced Queries to Aggregates. In general, the rule of thumb is: if you can use aggregates instead of advanced queries, then use them.

As for your last question, add two search parameters, and test them in the aggregate like the other parameter.
Hi Kilian

Really thank you so so much i'm happy :) this way is to easy from the Sql advanced i understand how you made all of this changes ..

best cheers
Hi Ilham,

Good to hear! I'm glad I could be of help. Good luck with any further endeavours on the platform :).
Hi Kilian

About my last question when i test serch by Date it's not working,so i'm trying to making change also on the refrechTable exactly on the asign node but no result,i think the serch by Date is deferent from the other attribute have you see this attachement please 
cheers  
Solution
Hi Ilham,

What you do in that filter is testing whether the date appears as text somewhere in a text string. "%" is the SQL wild card, so LIKE '%ABC%' means "a string that has 'ABC' anywhere in it". That obviously doesn't work with dates :).

I also see that you use the same session variable for both the Marque and the Date_Reparation (FacturationReperationDate1), which seems odd. What I advise you to do:

1) Add two filter inputs specifically for the from and to dates. Make it date fields, and add date pickers so the user can easily pick a date. Set the Null Value setting of the input box to NullDate(). Add two session variables to hold the values (e.g. Session.FacturationDateFrom and Session.FacturationDateTo).
2) In the query, add two filters, one for each date. The filter you already have must only include the check for Marque (so remove the "or" and everything after it, and use a session variable that reflects its use better). The first filter should be something like "Session.FacturationDateFrom = NullDate() or Fiche.Reparation.Date_Reparation >= Session.FacturationDateFrom" *, the second new filter "Session.FacturationDateTo = NullDate() or Fiche.Reparation.Date_Reparation <= Session.FacturationDateTo".

* Since NullDate() is actually 01-01-1900, and thus will always be smaller than the Date_Reparation, you could omit the first test, but I'd add it for clarity.
Solution
Hi Kilian

First of all i'll thank you for your help and your reply, then and about this issus i follow all of those steps but it's not ok,i add the pickers of the date but it's dosn't appear on the screen i don't know why !! i created two Filters on the preparation but i have one question about the agregate i have two the main one and the agregate where i have the SumTotal and the other attribute so when exactly i have to add those filters on the first one or on the last one?

cheers
Hi Kilian

it's dooooooooone thank youuuu so so much :) realy it's kind of you and all the teams of the community

best cheers of you all
Hi Ilham,

Great to hear you managed to make it work. Having help is nice, but doing it yourself is kinda satisfying. :)
Kilian Hekhuis wrote:
Hi Ilham,

Great to hear you managed to make it work. Having help is nice, but doing it yourself is kinda satisfying. :)
 Hi

Yes exactly all right,,,i want just one thing about this Date is how can i Omit or delete this date "01-01-1900" from the Filter input Please
Thanks
 
Ilham,

Fill in the Null Value property of the input with #1900-01-01# (the default date)
Yeah, I briefly mentioned that above, step 1 "Set the Null Value setting of the input box to NullDate()." :)
   :)  Yees yes exactly you mentioned that on step 1, i'm so sorry,it's Ok now
        Thank you Viera and you too 
Ilham,

You're most welcome :).
Hello

I have to Table records as you see last time each table has SumTotal ,so what i want now is to make a Total between this Two Totals,there is a way to make that please help

cheers
Hi Ilham,

Without knowing the details of what you want to do exactly, you could either make a separate query for that, or loop over the results and sum yourself (the latter is not advised in case there's a lot of rectods in the list).
Hi Kilian

what i want exactly to do is to calculate the :
Cout Horaire =(Amortisement_Materiel+Total_MainOeuvre+Total_Intrant+Total ConsomationGasoil)/nbr heur de Trvail
Knowing that i aleardy have calculated the Total_MainOeuvre and Total Consomation and TotalIntrant 
so how can i make this equation with Outsystem please

cheers
Hello
Cout Horaire =(Amortisement_Materiel+Total_MainOeuvre+Total_Intrant+Total ConsomationGasoil)/nbr heur de Trvail 
I made this Advenced query to calculate this equation have you see it please if there is an other way ? please :
SELECT {Engins}.[Marque],{Engins}.[Num_Ordre],sum({Entretien_Item_VC}.[Prix_Total]),sum({Fiche_Entretien}.[Montant_Total]),sum({Consommation}.[Prix_Total])

From {Entretien_Item_VC},{Fiche_Entretien},{Consommation}

inner join {Entretien_Voiture_Camion} on {Entretien_Voiture_Camion}.[Id] = {Entretien_Item_VC}.[EntretienID]
inner join {Engins} on {Engins}.[Id] = {Entretien_Voiture_Camion}.[EnginID]
inner join {Entretien_Voiture_Camion} on {Entretien_Voiture_Camion}.[Id] = {Fiche_Entretien}.[EntretienID]
inner join {Engins} on {Engins}.[Id] = {Consommation}.[EnginID]

WHERE
   sum({Entretien_Item_VC}.[Prix_Total])+sum({Fiche_Entretien}.[Montant_Total])+sum({Consommation}.[Prix_Total])+{CoutHoraireUpdate}.[Amortisement]
GROUP BY
    {Engins}.[Marque]
ORDER BY
    sum({Entretien_Item_VC}.[Prix_Total])
You could probably create an aggregate for that, as I don't see any special things going on. Also, I don't understand the WHERE clause, there's a calculation instead of a limitation there?
But i don't understand how can i make a addition between those Totals??!
Well, that's not so much an OutSystems Platform question as a basic programming one. I unfortunately don't have too much time to spare to answer such questions. Perhaps there's a collegue that can help you?