Manually Configuring Union tool for specific order

[LorenzTaylor]

Good Morning -

I am currently trying to union 4 different tables together which are labeled as 'tab name'.

Tab 1: QM Detail

Tab 2: QM Summary

Tab 3: Historical Detail

Tab 4: Historical Summary.

The Union Tool is set to "Set a specific Output Order" But it continues to list the tabs in alphabetical order with 'Historical Detail' set as the first tab on the output spreadsheet. Is there a way to set a specific order regarding the tabs when the Union tool is not working as intended?

[albert_alaluf]

Hi @LorenzTaylor 

Can you please provide some random data? I use Union, and the order I like it.

You just need to on the bottom side, Output Order, click on a set a specific output order and then play with the list (up and down the order you need).

[LorenzTaylor]

Please see attached sample data. Essentially each tab is a separate table which are then combined by the Union Tool. The Union tool is set to "Set a specific Output Order", but the output is not the order that I set. I've tried recreating the union tool multiple times and even changed the output order, Nothing seems to change.

[albert_alaluf]

OK. I think you want to write in that order in Excel tabs. 

So, the output order in Union, it will sort data the order you did.  The writing order in Excel, it is not always the order you want for some reason. You may try 2 things. You can either use tool container and output the order you like. Or, you can create an Excel template, and use overwrite it (overwrite sheet or range), so you won't lose your order.

[LorenzTaylor]

Would you mind providing an example using the test data? @albert_alaluf 

[LorenzTaylor · Accepted solution]

@albert_alaluf I was able to determine a solution. After the union tool, I created a new column 'Output_Order' using the formula tool with the following If statement: 

IF [TAB_NAME] = "QM Detail" THEN
1
ELSEIF [tab_name] = "QM Summary" THEN
2
ELSEIF [tab_name] = "Historical Detail" THEN
3
ELSEIF [tab_name] = "Historical Summary" THEN
4
ELSE
NULL()
ENDIF

I then attached the formula tool directly to the layout tool with the following configuration:

Layout Mode: Each group of records
Group By: 'Output_Order'
Orientation: Vertical with Section Breaks
Data field: Table
Section Name: Tab_Name

This configuration allowed me to sort by 'Output_Order' and the tabs were configured correctly.