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

Question

Question

Mileage Reimbursement- Database distance lookup

asked on August 24, 2018

I'm trying to see what is the best way to configure lookups in Laserfiche Forms for mileage calculation. Employees get reimbursed for traveling from branch to branch, so we created a database with the distance between each branch. We want to have a lookup so that when users enter the starting location and then enter the destination, the mileage will be prefilled. Is there a way to do this in Laserfiche Forms with the below database? Do I need to reconfigure the database to make this lookup work as intended?

 

Here's a sample of what the database looks like:

0 0

Replies

replied on August 25, 2018

You would need a record in the table for each start/end trip, if my math is correct you would have 30 records in the table. Your schema fields would be:

id   Start   End   Distance 

 

your lookup rule in the form would be basically select distance where start=start and end=end. 

1 0
replied on August 27, 2018

Thanks Glenn. What would the lookup look like in Laserfiche Forms (see below)? We have have close to 30 branches so this would be a lot of combinations to set up using the schema that you stated. Would you happen to know if there is a way to automatically reconfigure this table in excel or sql to display using that schema? 

0 0
replied on August 28, 2018

If I use this query in SQL, it brings back the mileage just how we want it to.

 

select rojas

from branch_distance

where Branch = 'Viscount'

order by rid

 

Just trying to see how I can integrate this query in Forms.

0 0
replied on April 10, 2024

I had to do this recently for a mileage lookup in Laserfiche Forms (Cloud) and wanted to share that I was able to take the customer's matrix spreadsheet and get it to work. I had to do a reverse pivot to take the matrix and convert it into a table with three columns, From/To/Distance.  Then you create a Lookup table in Laserfiche and import the converted table.  The next step is to create a Lookup Query where you pick the table and set the following for Select and Where:

 

You can set the 'Rows to Return' to '1' in case of duplicates and then create the Lookup Rule in your form and it will return the Distance value based on the From and To selections.  

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

Sign in to reply to this post.