Hi team, I'm trying to create a saved search that pulls all the data for work orders. I need to show the master work order, and all subsequent work orders and sub assemblies. I'm able to link the master work order at the line level by using this formula: MIN/*_*/({appliedtotransaction}) OVER (PARTITION BY {internalid}) : however it will not return the work order number when the status field is populated. How do I over come this issue? (edited)

AI generated answer (using internal Slack threads):

Based on the conversations, it seems like you're on the right track with using a formula to pull out the work order data. However, it's not clear why the status field is causing issues. One possibility could be that the status field is not properly formatted or contains null values that are causing the formula to fail. You could try adding a condition to your formula to handle these cases. For example, you could use a CASE statement to check if the status field is null or not formatted correctly, and return a default value in these cases. Here's an example of how you could modify your formula:

sql
MIN/*_*/(CASE WHEN {status} IS NULL OR {status} NOT LIKE '%-%' THEN 'Default' ELSE {appliedtotransaction} END) OVER (PARTITION BY {internalid})

This formula will return 'Default' if the status field is null or does not contain a dash (you can replace this with whatever condition fits your data), and the applied transaction otherwise. Remember to replace 'Default' and the condition in the LIKE clause with values that make sense for your data. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******