How can I load unique field(table) values into dropdown list

[oviland]

Hi,
I have an order table containing records of different items.
Each record has a field containing order number. The same order number can be in several records/order lines.
Is it possible to pull all unique order numbers and use them in a dropdown list?

The goal is to choose one order number and populate all belonging order lines in a list/embedded table.

How I imagen it could work.

1. Get unique numbers
2. Put them in a text arrey variable
3. Use this variable as input to the dropdown list.
4. Populate list

[gio]

Hello @oviland!!

That’s a great question, and there are several ways to approach it.

1. Tables API

The Tables API (https://support.tulip.co/en/articles/3983173-how-to-use-the-table-api ) can be used to get all records in a Table. You can then use the returned array to populate the options in a single select that can be populated from a Text Array (see Options & Variable fields on the Widget tab):

2. Create an ID Table

Create another Table with the unique ID’s of the Orders, and use that to filter the Order Details Table. Something along the lines of:

Display both the Order Details and Order ID tables in an app, link a Record Placeholder to the Order ID Table & add a trigger to store the selection in a Variable:

Filter the Details Table with the Order ID (on the Order ID field):

This is the result of this app:

Do either of these approaches work for what you’re trying to build??

Would you like to join office Hours this Thursday at 11AM EST (https://community.tulip.co/t/tulip-office-hours-10-15/791)?? It would be a great topic to cover. Let me know if this is of interest to you and I can add you to the invite!!

Gio

[oviland]

Thank you for your quick response @gio

Solution 1
Can you use the API to connect to a Tulip table?
All my data are within Tulip (at the moment)

Solution 2
I think I can use this solution. But instead of using Order Number, I can use a Ship-To field, which does not alter in the same degree.
The only problem here is that I would like to only show “Ship-To” that has waiting orders (but it can be accepted without this feature)
It is not an option to manually maintain a Unique order table.

Is there no way to pull/list unique field values from a table, or is this where the API solution comes in?

An option in an embedded table for “Only list unique”, based on a chosen field, would be handy.

[gio]

No problem @oviland!!

@oviland

Can you use the API to connect to a Tulip table?

Yes you can. You can create a Connector to your own instance, create a function that points to the specific Table you need to pull information from, and use it from your apps.

@oviland

Is there no way to pull/list unique field values from a table, or is this where the API solution comes in?

While there is not a way to filter out duplicates directly in using the API, you could iterate through the records and remove any from the array that are already present.

@oviland

The only problem here is that I would like to only show “Ship-To” that has waiting orders (but it can be accepted without this feature)

I may be missing some context, but I would like to point out that additional filters can be added to the Interactive Tables.

Hope this helps, it would be great if you could share your app on https://community.tulip.co/c/show-and-tell/9 once it’s built as it sounds very interesting!!

Gio

[DariusM]

@gio

While there is not a way to filter out duplicates directly in using the API, you could iterate through the records and remove any from the array that are already present.

How do you achieve this?

[gio]

hello @DariusM, this video shows you how you can iterate through an Array:

Subtracting BOM Line Items from Inventory Table

you will need to add some logic to only remove duplicates, but this guide should get you started in the right direction. let us know if you have questions as you start building the solution!!

[DariusM]

Thanks for your quick reply. I have managed to iterate through each item, but I did not find any way to remove the duplicates. It should implement a logic similar to this pseudo code.

j = 0

// traverse elements of arr
for i=0 to n-2
// if ith element is not equal to (i+1)th element of arr, then store ith value in arr[j]
if arr[i] != arr[i+1]
arr[j] = arr[i]
j += 1

// store last value of arr in temp
arr[j] = arr[n-1]
j += 1

Do you have a working example? Thank you in advance!

[gio]

hello @DariusM, no problem!! I unfortunately do not have a working example. could you share a screenshot of the Trigger where you’re attempting to remove the duplicate??

furthermore, could you share a little more context on the use case?? there may be alternatives (including using Tulip Table Queries and Aggregations | Tulip Help Center - Support for Building Manufacturing Apps) to achieve this.

[DariusM]

The goal is to populate a dropdown with unique values from a table column. I have used the Table API approach to load the output into an array and managed to populate the dropdown, just that the values are not unique.

In the trigger I have managed to get the current element, but I would need to add a nested if and then compare the value with the next element in the array…

[gio]

hello @DariusM, have you tried using the Contains function in the If condition in your Trigger?? something like this should help in what you’re trying to build:

[DariusM]

It does not work @gio . If I use the “If” it only work one iteration, but in my case I need to loop through the array? any other thoughts? Thanks.

[Grant]

Hey @DariusM, I think I have a solution that could work for you in the short term, but it’s a fairly complicated workaround. Hopefully, we can use these learnings to get the appropriate feature requests prioritized!

Here’s a recording of what I’m doing, with each step laid out separately to show the return.

As shown, there are four key steps needed to perform this.

1. Pull the order list using a Tulip API (limit is set to 100 rows)
2. Convert to a string with a common delimiter
3. Utilize a SQL server to help eliminate the duplicates (does not need to store to a table)
4. Convert the return SQL array to be suitable for a dropdown.

Now these steps don’t all have to be visible to the user, but I wanted to show what each one was doing.

The first button just runs a Tulip API connector function to grab some set of orders, which can be filtered as needed. This endpoint would obviously be specific to your own instance, tables, and column information.

The second button is simple, we just run a trigger for Array > Join to String, where we join our API return by a static delimiter (I used the pipe symbol “|”).

With our list of orders now joined as a single string, we can execute the third button which is the most complex. I’m not a SQL expert, so I’ve tried to add some comments to the logic to make as clear as possible, but there may be better solutions here. Ultimately, we want to take a string like WO-0001|WO-0002|WO-0001 and return WO-0001|WO-0002.

The SQL query essentially takes in the joined string, separates it to an array, and uses the unnest function to map this to a set of rows. From that subquery, we can then select distinct values before sending back as an array of unique orders. It’s worth mentioning that I’m using the Connectors Demo connector here, which is a PostgreSQL database.

Once we have the return, we just need to run our last button, which just uses the MAP_TO_TEXT_LIST expression to format this in a way that is compatible with the single select drop down. You just pass in the array and your key, in this case the name of your output, and it should store to a variable that can be linked to the single select!

I know this is a lot of info, please let me know if you have any questions and if this is helpful! Hopefully this can help unblock you in the short term while we work on better native support.

Thanks!
Grant

[DariusM]

Thanks Grant for providing me the steps. It worked!