You are viewing limited content. For full access, please sign in.

Question

Posted to Laserfiche Lounge

Question

Laserfiche Lounge

Dynamic Fields - how can you use more than 1 External table?

Laserfiche Version 9 Integrations
Updated August 24, 2016
asked on August 22, 2016

Hi, I am looking to utilise data from 3 different External Tables (SQL Views) and apply Dynamic Fields across selected External Column values in Laserfiche.

Strangely Laserfiche doesn't appear to allow a dynamic relationship to more than 1 External Table - ? - which forces you have to have one huge table of information which is slow to load and after a while crashes Laserfiche 9.2.  One table = 17,000 rows returned which isn't a lot in data terms.

How can we get around this?

I want 3 separate tables dependant on each other (in a Parent/Child type arrangement).

Does anyone have a fix in Laserfiche or SQL for this please?

Thank you
Ralph

 

 

 

 

 

0 0

Replies

replied on August 22, 2016

Have you tried using a stored procedure? I had to do something similar where based on single input i wanted to pre fill information that was coming from more than one table. 

I was able to achieve it using a stored procedure.

Hope it helps.

1 0
replied on August 23, 2016

Thanks Junaid, we will look into that.   Thank you Ralph

0 0
replied on August 22, 2016

Can you give a specific example of what it is you are attempting to do? We already allow you specify a distinct external table for each field within a template. Are you trying to use multiple tables for just one field?

0 0
View 2 previous replies
replied on August 23, 2016

Hi Brianna –  dynamic fields works well in Laserfiche if you have all your data in 1 external table, however it cannot handle thousands of rows of data without crashing.

You can link indeed any individual field to any external table in Laserfiche.

However, you cannot then use the Dynamic Fields to sort the “relational/dependency” order in which the Laserfiche Parent Fields should then apply to those fields.

So if I wanted to have the following 3 field names …..

  • Client Names (retrieved from ClientMattersTable)
  • Client Bankers (retrieved from Clients Table)
  • Client Bank Account Numbers (retrieved from Clients Table)


with the following field dependencies/relations

  • Client Bankers will be dependent on Client Name selected in template
  • Client Bank Account Numbers will be dependent on Client Name>Client Bankers selected in template


…….how do I achieve that using multiple external tables?

We get the attached error [9370] message when trying to link fields from just 2 external tables.....

Thank you
Ralph

0 0
replied on August 24, 2016

HI Ralph,

We have similar situations where we use data from multiple SQL tables. In such instances we created a SQL View that then combines the relevant columns from each table we need into one, and then used the "SQL View" as the referencing external table in Laserfiche. This then allowed us to point to one External table even though the actual data resides in multiple SQL Tables.

Your challenge however might still be the number of records returned. Depending on your scenario, you could then adjust the SQL View to only contain a smaller number of records that would work better in the Laserfiche Client.

Hope this helps. 

3 0
replied on August 24, 2016

Hi Ralph,

 

Just something to add to this, when using a view over a static table, it is the retrieval speed that can be an issue. When you access data through a view it has to query the tables on the fly before presenting you with the results. Whereas a static table, there is no calculation required.

 

smileyyes

1 0
replied on August 24, 2016

Thanks Vincent, yes we have 1 external SQL View that combines many SQL external tables from another database - which is perfect for Live updating of data/values in Laserfiche and works really well.

It is just the volume of rows returned (17,000 and counting) in 1 SQL View that seems to upset Laserfiche stability.

Therefore we were hoping to minimise the rows searched/returned by splitting it into probably 3 SQL Views, to improve processing speed and hopefully stop it crashing.

Laserfiche does not appear to permit Dynamic Fields to be used for more than 1 External table/SQL View though - it gives the [9370] error above.

I am hoping that Brianna from Laserfiche.com can clarify/has a resolution for us......

Thanks for your input though, good to know others use SQL Views too :)

Ralph

 

 

0 0
replied on August 24, 2016 • Show version history

Thank you for the example and error message. It does not look we like support using a column in one table as the parent for a column in another, though I'll confirm with someone on the team that works on this functionality.

I expect attempting to change this functionality would require fundamental changes to how the values are retrieved, so I recommend looking into SQL side optimizations/changes, such as modifying the view as Vincent suggested.

One final note:

I see that you have two field (Account Number and Parties) referencing the same column, but one is a parent of the other. That is also not supported, as mentioned in the error message: "the parent field must be present in the same external table but use a different column from that table".

From your example, I don't understand why you have it set up this way. Some sample values would clarify the use case, and perhaps others with similar scenarios could offer their solutions.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.

Details
Related Posts
Loading ...