How to reference specific array items in Saved Search Results Filter?

Hello,

 

I’m trying to essentially list the details for every line item on each invoice in a saved search that I ran, ideally I would like each detail of these to populate in their own line in the search results, my issue is I cannot find a way to reference the specific items in the formula.

 

Any ideas how I could move forward with this?

 

Thanks,

Beginner Asked on January 31, 2020 in Administration.

Your question is easy enough that I suspect you arent fully explaining it. You can add a standard item criteria (with an anyof operator) if you want to limit items. And you can add an item result column if you want items for your invoice to appear on different rows.

on January 31, 2020.

@battk I was concerned I may not have elaborated well enough myself. I’ll go through my circumstance with a bit more detail.

 

  I have a search I have designed where we want every invoice with XXX-XXXXX Item number, the issue is that some invoices have multiple entries of this item with multiple different details (description, price, etc) The criteria of what I want to pull is correct, it is how I present it in the results.

There is multiple item filters and subfilter filters, but all seem specific to one item and do not allow any reference of different line items on a list of items in an invoice.  

 

An example of what I would like to be able to do is have a formula similar to:  

CASE WHEN {item.2} = XXX-XXXXX THEN ‘<font color=”#BE2625″ size=”+1″><b>’||{item}||'</font>’ END  

But I have found no information of how to reference specific line items in a list.

on January 31, 2020.
Add Comment
1 Answer(s)
Best answer

Sounds like you want to use an analytic function like ROW_NUMBER (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions137.htm) or DENSE_RANK (https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions043.htm).

As an example, if your the formula was: ROW_NUMBER() OVER (PARTITION BY {internalid} ORDER BY {linesequencenumber})

the column would consist of sequences of row numbers grouped  by the internal id where lower line sequence numbers would have lower row numbers.

Intermediate Answered on February 1, 2020.

Well not really, I stumbled across the formula ROW_NUMBER() OVER (PARTITION BY {internalid} ORDER BY {linesequencenumber}) that you mentioned, but what this does is simply state, from the results, which item sequence number each item is.

Stepping away from the background reason for me needing to do this, the question is simply: Is there a function where I can specifically reference say, line item 2 from an invoice?

That is my question, how to reference specific line items in a formula to edit how that data is displayed. {linesequencenumber} gives me the number which is great for informative purposes, but what If I want/need to do more with that number?

Would the only way be to go like CASE WHEN {linesequencenumber} = 2 THEN ‘Turtle’ or something?

on February 3, 2020.

{linesequencenumber} would give the row number for the transaction record. ROW_NUMBER() OVER (PARTITION BY {internalid} ORDER BY {linesequencenumber}) would give the row number for the search results. Pick whichever one works for your purposes.

Combine either formula with a CASE statement to do your work. I would recommend the simple form of the CASE.

CASE ROW_NUMBER() OVER (PARTITION BY {internalid} ORDER BY {linesequencenumber}) WHEN 2 THEN ‘Turtle’ END

on February 3, 2020.

I gotcha, and I was thinking that direction, but it would take quite a bit of tweaking for some options I had in mind.

 

I guess it would be too easy for Oracle to add a {item_line.2} or something similar lol.

 

Thanks for the help man.

 

Appreciated.

on February 3, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.
  • This site made possible by our sponsors:   Tipalti   Celigo   Limebox   Become a Sponsor