Join tables from dropdown and button group

Join tables from dropdown and button group

  

Hi,

I'm making a mobile application that uses inputs to deliver the final result from the database. I have a problem when I connect two databases, which I need to merge to get to the final result (PRICE in Table 2).

1. I have to show NAME and YEAR (Table 1) as dropdown but do not show duplicate data

2. I have to display COLOR (Table 1) as a buttongroup so that if a user selects "Cat" in a dropdown the buttongroup should show "Black, Blue, Yelow" from Table 1.

3. As a hidden input I have to display the PRICE from (Table 2) as an example if the user chooses "Cat (from dropdown) Blue (from buttongroup)" applications must display the price "261" as a result.


I tried all possible ways, I searched the forum for days but failed. I want to mention that Table 2 contains 3,000 records, I hope this does not represent a problem with the final result (PRICE)

btw Thank you

Amx wrote:

Hi,

I'm making a mobile application that uses inputs to deliver the final result from the database. I have a problem when I connect two databases, which I need to merge to get to the final result (PRICE in Table 2).

1. I have to show NAME and YEAR (Table 1) as dropdown but do not show duplicate data

2. I have to display COLOR (Table 1) as a buttongroup so that if a user selects "Cat" in a dropdown the buttongroup should show "Black, Blue, Yelow" from Table 1.

3. As a hidden input I have to display the PRICE from (Table 2) as an example if the user chooses "Cat (from dropdown) Blue (from buttongroup)" applications must display the price "261" as a result.


I tried all possible ways, I searched the forum for days but failed. I want to mention that Table 2 contains 3,000 records, I hope this does not represent a problem with the final result (PRICE)

btw Thank you

Hi AMX,

When you say you have connected two database, Do you mean two different database or two different tables from the same database.

Also by reading above information I am still not clear what exactly you want to achieve. If you can explain the business case, would help us to understand in more detail.Also can you please share sample oml for reference.

Sachin

Hello amx,

I'm still not really sure about waht exactly you are trying to achieve...

For the 1, I don't see any problem, as you can always group by name and year. Of course, you will lost the other attributes (ID, Color and Size). If you don't need to SHOW them, you can use an aggregate to group by those two fields, and this will remove duplications. You will have to return, as result, the YEAR, as this is the connection to the table 2.

In 2, you can put the group box inside a block, with an input parameter that is the year that is associated to the chosen item in the drop down. Every time the user selects a year in the drop down, the Parameters Changed event of the block will be triggered. You can them refresh an aggregate, filtering table 2 by the year, thus getting the required fields, that will be used for your button group. 

In 3, every time you select a group button, you can find the price relative to the clicked button, as you can fetch it from the table, or maybe store in a local list and fetch it them, triggering an event that will allow the page to update the input with the price you fetch in the block.

Again, not sure what you want, but if is like I think, you can do as above.
Exactly what did you try?

Cheers.


. oml file in Attachment

- By database yes i meen tables sorry :)

- Dropdown must be displayed by NAME and YEAR (but not duplicate) I've grouped but without success (i can display only one group)

- The COLOR should display on buttongroup depending from dropdown abowe

- After the user saves the form in Result screen he it needs to display PRICE from Table2

sorry for my bad English

"- Dropdown must be displayed by NAME and YEAR (but not duplicate) I've grouped but without success (i can display only one group)"


I'm expecting you  want to list the table 1 in the dropdown in this manner:

CAT -  2017

CAT -  2018

...

You can write a query to list this:

SELECT DISTINCT (name + ' - ' + CAST(year as varchar)) as value FROM table1


Then attach the list to your dropdown.



"- The COLOR should display on buttongroup depending from dropdown above"

BInd an onChange event on your dropdown, get the selected value then split the value with '-' so you will get 2 elements. Expect the first result as the Name and the second is Year.


Write a query to get the color base on these selected values.




Jovvy wrote:

"- Dropdown must be displayed by NAME and YEAR (but not duplicate) I've grouped but without success (i can display only one group)"


I'm expecting you  want to list the table 1 in the dropdown in this manner:

CAT -  2017

CAT -  2018

...

You can write a query to list this:

SELECT DISTINCT (name + ' - ' + CAST(year as varchar)) as value FROM table1


Then attach the list to your dropdown.



"- The COLOR should display on buttongroup depending from dropdown above"

BInd an onChange event on your dropdown, get the selected value then split the value with '-' so you will get 2 elements. Expect the first result as the Name and the second is Year.


Write a query to get the color base on these selected values.




Thanks for the instructions Joovy

It helped me a lot if you could get this into . oml file that I attached, it would help me a lot.


Hi AMX,


I don't have the right version of a service studio installed on my computer right now. My current Personal Environment is OS 11, so I can't open it.

What you could do is:


  1. Instead of using Aggregate add a Data Action on your Mobile Screen.

  2. Inside of the Data Action, add an Advanced Query, then copy and paste the query I have given earlier.

  3. Make sure you have the right Structure, Create a Structure that expects 1 Column.

    Test the Query, to see if it's actually you intended to do.

  4. Add an output parameter for Data Action then use the result of the Advanced Query.

Hi AMX,

I have seen the code in your oml and If i understood correctly, You want to show the prices on result page based on selection you have made from by selecting the values from the dropdown & it's respective button group.

Also I am agree with jovvy's comment, You might expecting values in dropdown like he said.

What l think you must have two dropdown..in first dropdown you should show pet's name and in the second you can show the year.

To make your database more meaningful & relational, You can design as per below ERD. I think it will help to solve the business case which you are looking for. 

 

Let me know if you want me to make changes in your oml.

Sachin

Hi Sachin,


Yes, you understand what I want to do, if I have to do 2 dropdown, then OK let's do it.

Then the solution should be two dropdown (NAME and YEAR) and the button group to display a COLOR dependent on the previous dropdowns. The result should be PRICE

Thank you

Solution

Amx wrote:

Hi Sachin,


Yes, you understand what I want to do, if I have to do 2 dropdown, then OK let's do it.

Then the solution should be two dropdown (NAME and YEAR) and the button group to display a COLOR dependent on the previous dropdowns. The result should be PRICE

Thank you

Hi AMX,

Please find the sample oml attached. Give it try, It may solve your problem now.

Let me know in case you find any difficulty 

Sachin


Solution

Sachin Mahawar wrote:

Amx wrote:

Hi Sachin,


Yes, you understand what I want to do, if I have to do 2 dropdown, then OK let's do it.

Then the solution should be two dropdown (NAME and YEAR) and the button group to display a COLOR dependent on the previous dropdowns. The result should be PRICE

Thank you

Hi AMX,

Please find the sample oml attached. Give it try, It may solve your problem now.

Let me know in case you find any difficulty 

Sachin


Thank you Sachin but the drodown dependency and result (PRICE) doesnt work

There should be two screens, one input and second result.

On input user selects, on result the price should list depend from input (dropdown and button group)

Please help.


Hi AMX,

I had implemented the same way. On input screen you can select the pet from first dropdown & Year from the second one. Select any one color from the button group and when you click on Show Prices, You will get redirect  to ShowPrices screen where you can see the prices based on the selected inputs.

Correct me, If I have different understanding.

Sachin

Sachin Mahawar wrote:

Hi AMX,

I had implemented the same way. On input screen you can select the pet from first dropdown & Year from the second one. Select any one color from the button group and when you click on Show Prices, You will get redirect  to ShowPrices screen where you can see the prices based on the selected inputs.

Correct me, If I have different understanding.

Sachin

You have done it on your way but it helped.


Thank you :)


Amx wrote:

Sachin Mahawar wrote:

Hi AMX,

I had implemented the same way. On input screen you can select the pet from first dropdown & Year from the second one. Select any one color from the button group and when you click on Show Prices, You will get redirect  to ShowPrices screen where you can see the prices based on the selected inputs.

Correct me, If I have different understanding.

Sachin

You have done it on your way but it helped.


Thank you :)


Great..it worked for you :)

Sachin