LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: the1don

We are running two env currently. 4.6 and 6.1.

Every 15 minutes I have a random number of records as input. There could be zero records to currently the max has been 20 records. The records are one column which is a number.

Example input
Account
306759
452109
1023410

I take each number and run three database queries.

select field1, field2, field3, filed4
from account
where account = :1

select loc1, loc2, loc3 loc4
from location
where account = :1

select cust1, cust2, cust3, cust4
from customer
where account = :1

I want to create a different Excel spreadsheet for each account (Excel_306759, Excel_452109, Excel_1023410) and each spreadsheet would have 3 tabs: account, location and customer.

If I take one record I can use the Excel node which allows me to create the 3 tabs but I can't figure out how loop through the records to create a different Excel spreadsheet dynamically. Since the account numbers will be different each time and the number of accounts could be different each time.

Any thoughts would be appreciated.

Thank you
Don

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mlam

Hi Don,

Do you need a solution to work in both environments or either?

In your 6.1 environment, you would have access to the Do While/ Do While Conditional nodes, which allow you to loop through your data based on user-defined criteria (such as unique accounts).
There's a pretty good example in this thread that is hopefully useful: http://community.lavastorm.com/threa...multiple+excel

In your 4.6 environment, you don't have the node looping as above but you can have a graph that passes the unique account numbers, based on the data, to an Execute BRX node, which is in turn then writing the Excel file. Attached is a simple example of using Execute BRX:exampple execute brx.zip

Michelle

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: the1don

I would need it for both environments right now. We have one server set up on 6.1 where we are migrating our graphs with an estimated production migration date of Nov 16th 2016. Until then everything needs to be developed on 4.6 and 6.1.

Thank you Michelle

I will give this a try.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mlam

Here's another good example that my colleague, Stony, put together based on your query: DbQuery Loop.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: the1don

This is another great example and does exactly what I need in 6.1. Switch is a 6.1 accelerator correct? I do not see that functionality in 4.6. I am trying the parent/child approach that you mention above.

Thank you booth
Don

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

Switch is a new addition to the CORE library.
It's not a stand-alone node.. it required special integration into the "controller" that decides what sequence to execute the nodes in.
Unfortunately, it doesn't exist in 4.6

===============
There is a completely different structure that could be used in 4.6 - You could use the ExecuteBRX node.

Basically, you create a "child" graph that receives the Account# as a parameter, run the queries and outputs the Excel. You would save this graph as a BRX (compiled executable).

Then you create a "parent" graph that has the list of account#'s which feeds into the ExecuteBRX node.
The ExecuteBRX will then loop thru the list, running the child graph multiple times.

I've used this method for years and it works well. The one issue with this method is that you have to maintain two separate graphs.
The good news is that method is forward-compatible, you could use the same logic in 6.1 - you'd only need to build an updated BRX for the child.