Skip to main content

Get numeric value from other database instead of Join

Hi,
Can you help me with a script to obtain a value from other table, or populate a temporal array obtaing values fromo other table using a common code_type field on both tables as criteria to search value range

I have a Account Receivable, and need to obtain a percent value from other table (table B) using the client_type and age_range (lets say 90 days) from table A, so I need to search wath %value has the table B comparing  the client_type.
Example
Table A
Type : 3, age: 500 days

Table B
Type 3 - days: 30 percent: 10
Type 3 - days: 90 percent: 20
Type 3 - days: >90 percent: 50

So I need to obtain the 50 from table B, on Table A

I was reading this script, as example but have some sintaxis error
https://ideascripting.com/forum/obtaining-data-database-use-selection-c…

Thanks for help, I'm so rusty on programming (more than 15 years no programming, I used to programming on vBasic, SQL, foxbase, pascal, clipper and C++

Brian Element Thu, 11/07/2024 - 06:36

Hi gmoalfaro,

I am first doing this in IDEA to make sure it is what you are looking for.  After that it can easily be made into a script.

I first created your two files for table 1 and table 2.

Image
Get numeric value from other image 1

I then added a virtual field to both of them for the days field.  This field I used for the join.  In the virtual field I used a @compif so that if the days is less than or equal to 30 I would have a 1, less than or equal to 90 would be a 2 and anything over 90 would be a 3.

Image
Get numeric value from other image 2
Image
Get numeric value from other image 3

For table B the @compif is for a character field as I used >90 to indicate days over 90 days, so you might have to adjust the @compif as necessary.

I then did a join based on the type and new fields (DAYS_JOIN).

Image
Get numeric value from other image 4

This is the final result.

Image
Get numeric value from other image 5