Text() function on fields with leading zeros causing bad associations

sstefancies — Fri, 28 Oct 2016 13:35:19 GMT

I have a problem with one field across multiple tables: Cust-no. In the database, this field is a varchar array so there are often two separate customers that are numerically equivalent.

Current example: 00020047 and 20047

These two customers have completely different profiles and associations with other tables. I’ll keep this example simple and only explain the problems I’m having associating my customer table with my invoice table.

Without making any adjustments to the LOAD script, all of the invoices are doubled, one for each customer. Each customer is assigned the same Cust-no: 00020047. 20047 disappears. This is problematic across all tables as each invoice in this scenario shows as having two different customers:

I’ve tried to resolve this using text() in the load script. I put it in both tables, customer only, and invoice only with the following results:

Both tables:

Invoice table > text("Invoice.Cust-no") AS "Cust-no", (this loads from a qualified QVD)

Customer table > text("Cust-no") AS "Cust-no",

This appears to work at first, creating two separate customers with two different Cust-nos.

However, the association fails. All the invoices for 20047 belong to 00020047.

This is inaccurate as 20047 is the Cust-no on many of the Document numbers. 20047 shows as having no invoices.

Using the text() function on only the invoice table yields the same results.

Using text() on only the customer table breaks the association completely. Both 20047 and 00020047 have no invoices associated with them.

Is there anything I can do in the select statement or some other function that I could use in the preceding load to force the Cust-no the be a string without breaking the association to other tables?

Re: Text() function on fields with leading zeros causing bad associations

sstefancies — Tue, 22 Nov 2016 17:24:53 GMT

I was able to resolve this problem by eliminating QVDs from my data model.

topic Text() function on fields with leading zeros causing bad associations in App Development

Text() function on fields with leading zeros causing bad associations

Re: Text() function on fields with leading zeros causing bad associations