How to create a header-detail connection from an imported table

[Manos Pappas]

Hello,

I have a Jam v5 project using MSSQL as database backend.

The project has an SQL view that fetches data from some other tables and uses as primary key a UUID. The view is guaranteed to have distinct records, therefore each UUID (and the record it represents) is unique.

This view is imported into the Jam project and is used as a "header" table for invoices.

My question is this: how can I create a details table that will connect with the imported header table using the UUID field as the Master Record ID, i.e have a 1:N relationship with each details record having the UUID as the master? I am considering using a mapping table where each UUID will have a specific integer ID and then have the SQL Server view join to that table, so that the final imported view will have a fixed INTEGER ID field for each UUID.

I was wondering if there is a simpler method that one can use to do what I want.

Many thanks in advance.

Best regards,

Manos

[Dean D. Babic]

Hi,

check your email, can't remember what I put there...

[Manos Pappas]

Hi,

You've sent me an e-mail directing me to this article  https://groups.google.com/g/jam-py/c/rj0VuRqdYk8/m/H3oflisYAwAJ

I need to re-read the article a couple of times because I still cannot understand how to do it.

The only reason I am using a view/imported table is because what I actually want is to have a N:N relationship between the master table and the details table.

The master table - which is actually a Jam-created table with the ID integer field - has also a UUID field.

This field is populated each time a NEW record is inserted but when a master record gets cloned (the master table contain invoice header data) to a new record, the UUID of the copied record is retained.

Therefore, we have multiple master records with the SAME UUID that each of them should be linked to a Jam-created details table, thus the need for N:N relationship.

M.

[Dean D. Babic]

Hi Manos,

I think you are referencing Headers as a Master table, correct? I am not sure that I understand though.
If you have Imported table, that table does not necessarily act as a master. Or if imported as a Detail, act as one!
Plus, TXT UUID PK is not really supported in v5, I think. Hence, not sure how would that do a lookup.

I would visit this thread as well:
https://groups.google.com/g/jam-py/c/psWdvelzjPE/m/rLbzN3KQCwAJ

If you clone the master record, and UUID is the same, this is repeating the same data, so what is the point?

You might clone the PK as well, if is not enforcing uniqueness. Same thing. 

If u need that UUID in Details, than this is your  master_rec_id.

But if you are saying that cloning master record keeps UUID the same, than how can a detail find it's master?
Because there are too many! Detail needs a UNIQUE master.

I think you might need to separate UUID as a lookup for Master PK ID, if u are saying that it's the same, and MUST be the same.

Then your Detail would reference Master as usual, and also get the same UUID from Master.

Hmm. Clear as mud. Anyways...

[Manos Pappas]

Hi,

It's not you Drazen, it's me that I do not explain correctly...

The master table which is indeed headers as you suggested, is not imported, it is a Jam v5 table.

The table has cloned records that have different PKs and other fields - thus they are not exactly the same - but all cloned records have the same UUID text.

So, while they are different records for the header table, they are the same record for the details table (remember, the details table - which is also a Jam created table - should refer to the UUID as it's master, not the PK of the master).

Bummer...

[Dean D. Babic]

But that is exactly what I'm thinking, use in Detail the UUID as master_rec_id. Not the PK.
The problem is the TXT for that lookup, not the different field as a master_rec_id replacement, if the field is INT.
I still don't get it what exactly are you cloning? Master or Detail or all together in one go?

Because if you cloning any of it, and you say UUID is kept the same, then all referential integrity is gone if 
not referenced to a PK but UUID!
Sorry, very confusing. Record the sesh, by using Chrome integrated translation to English, and will go from there.

D.

[Manos Pappas]

Hi,

The master table has the following fields:

ID (int, set by jam, PK)

Name (text)

YearID (integer)

....

GID (text, UUID)

The details table has the following fields:

ID (int, set by jam, PK)

TaskDate (DATE)

Notes (LONGTEXT)

...

master_rec_id (text, UUID)

As you correctly said, it is not possible to define common fields for the Details group (the one that will contain the details table) other than of type INT.

In my case, I would like the master_rec_id field of the details to contain the master.GID UUID value.

The idea is that the master table will contain header data and the details table will act as a journal for the master, meaning that master items (N) with the same GID should "connect" with the same detail data (N).

Again, thank you for your time in helping me.

M.

[Dean D. Babic]

Ok, 

"The idea is that the master table will contain header data and the details table will act as a journal for the master, meaning that master items (N) with the same GID should "connect" with the same detail data (N)."

- will do what?

Are you trying to identify which Detail is exactly in which Master and display this somehow? Sounds like this is a candidate for a Keys type. 
I'm not sure how else tho.

D.

[Manos Pappas]

Hi,

I gave up with the whole idea, no matter what I do, Jam will always try to lookup using the PK of the other table.

I will try a different approach by creating the details table as a normal Jam item table but not an actual "detail" table.

It will need some custom code in order to do what I want but I will post the solution once is done.

M.