Can I display a DataLink with no results?

Sean McClurkan

29 Juli 2016 16:00

Essentials 4.5.1.58 HTML 2.6.1

 

Is there a way to have a Data Link display when there are no resuts?  

 

We have a viewer that profiles several items for a specified address.  We use Data Links to show the results, but if there are no rows returned from the Data Link, then the entire Data Link - e.g. the title of the link - doesn't show at all.  Our users want to see, explicitly, that there are no Future Annexations or Utility Accounts at an address when there are none.

 

Let me know if there is a JavaScript configuration setting I can use to return the empty Data Link result with a message 'No Future Annexations Found' or if you know a clever SQL function that will recognize No Rows Returned and replace that with a message.

 

Thanks!

0

• 

  John Nerge

  05 Augusti 2016 13:24

  So if I understand correctly, right now you're using the data link as the title of your search result, so if there's no match on the data link you get a search result with a blank title?

   

  If that's true, then how about using a CASE statement in SQL to handle? Something along the lines of

   

  CASE

   

  WHEN [AnnexationField] is null THEN 'No Future Annexations Found'

   

  ELSE [AnnexationField]

   

  END AS [Annexations]

   

  or, if you're returning a count of events, then

   

  CASE

   

  WHEN Count([AnnexationField]) = 0 THEN 'No Future Annexations Found'

   

  WHEN Count([AnnexationField]) = 1 THEN '1 Future Annexation Found'

   

  ELSE Cast(Count([AnnexationField]) AS VARCHAR) + ' Annexations Found'

   

  END AS [AnnexationsCount]

  0
• 

  Sean McClurkan

  05 Augusti 2016 13:44

  The title of the Data Link only appears if data is returned from the SQL.  The first selected column appears as a list item.  If no rows are returned, then the entire Data Link is ignored and nothing is displayed.  I needed the Data Link to be displayed with a message letting the user know there was no data for that link.  I ended up with the SQL below which uses an inline view named "x" unioned with a query that returns the message I need WHERE NOT EXISTS any data from the inline view "x".  See below:

   

  WITH x AS

   

  (SELECT initcap(REPLACE(a.account_name, '"')) "Account Owner"

   

                ,initcap(p.place_text) || ' ' || initcap(p.city) || ', TX  ' || p.zip_code "MailingAddress"

   

    FROM coa_address p

   

   INNER JOIN ccb_account a

   

      ON (p.place_id = a.place_id)

   

   WHERE p.discontinue_date IS NULL

   

     AND a.address_place_id = :placeid)

   

  SELECT * FROM x

   

  UNION ALL

   

  SELECT 'No Utility Account Found' "Account Owner"

   

         ,NULL "MailingAddress"

   

    FROM dual  

   

   WHERE NOT EXISTS (SELECT * FROM x)

   

  This only executes the base SQL statement once, as oppposed to just repeating the first statement with a WHERE NOT EXISTS.  Now, when there are no Utility Accounts for an address, the title of the Data Link appears in the panel with the message "No Utility Accounts Found" when there are none.

  0

Du måste logga in om du vill lämna en kommentar.