Which field type should I use? Tuple? Text with assignment, lookup to main table?

[Former Member]

Hi experts,

I'm starting a new project and my customer wants to create a database for the info related to materials.

Into the main table, we want to have fields related to materials, for example:

• Material number (auto ID)
• Material short description (text)
• Material long description (long text)
• Manufacturer (lookup without format)
• Manufacturer Reference (text)
• Info record (qualified lookup)
• Source list (qualified lookup)

IThe problem comes when I create the info records qualified table, like in SAP R/3, which is composed by:

• Material number (no qualifier)
• Supplier (no qualifier)
• Valid date interval (from / to) (no qualifier)
• Amount (qualifier)
• Price base quantity (qualifier)
• Price base quantity UoM (qualifier)

My problem is how to inform the material number into the qualified table with the same number like into the main table.

It is possible to have different Info records for the same material (with different suppliers, different date intervals, etc.)

Which solution do you think it is better?

I don't like the option of creating a lookup table for the material numbers and then in main table or info records table create the material number with type 'lookup (without format)'.

I've tried to create it with type 'tuple'¡, but I think it only allows one entry.

I've seen that there is type 'lookup (main)'

I've also tried to create an assignment in info record for including the 'material number' from main table, but there are available only the fields from the same table. The same happens with 'Calculated' fields, you can only access to the fields from the same table.

Do you have any suggestion?

Have you seen this problem before? how did you solve it?

Thanks

Best regards,

Carlos Santamaría.

[Former Member · Accepted solution]

Hi Carlos,

Why do you want to have a field in the Info record qualified table which refers the Material Number in the main Table?

My understanding of your requirement is that you want to assign multiple info records to one Material in the main table.

With the same data model as described above by you, You can create multiple info records and you can assign multiple Info Record's (Type: Qualified) to one Material Number. since qualified fields are always multi valued.

Please let me know if your requirement is other than what i have understood.

Rgds,

Prasad

[Former Member · Accepted solution]

Hi Prasad,

As you say, I want to have different Info Records for the same Material in the main table.

My problem is that the Info Record is composed mainly by the Material number and the Supplier, then I think it's necessary to have the material into the Info record table.

As you can see, I've created the repository by creating two Tuples, one related to the other.

Thanks in any case for your help!

Regards,

Carlos Santamaría.

[Former Member · Accepted solution]

Hi Carlos,

In that case you can create a Multivalued tuple field in your main table.

Tuple field values are stored with the main table records.

So first create a tuple(Info Record) with the following fields

• Supplier(Text)
• Valid date interval (from / to)
• Amount(Text)
• Price base quantity(text)
• Price base quantity UoM(Lookup)

Do not include the Material Number field since the tuple data will be stored with the main table record.

Then in the main table create a field of type tuple(Multivalued), looking up into the Info Record tuple

Now as per your requirement you can have multiple inforecords for the same material. and since they are created along with the main table record(i.e along with the Material) you do not need to create a seperate field called Material Number in the Tuple. and your Inforecords will exist along with the main table record.

Similarly you can also create Source list as tuple.

Please Note that the tuple data cannot be reused for the other records, that is, each time you create a new Material in the main table, you have to populate the Inforecord tuple data.

Thanks and regards,

Prasad

[Former Member]

Hi Carlos

If you want to see different infor records you can use multivalued Lookup in you main table

one for supplier another one for customer etc.

Usually if new values come or update by first step you create it(or update) in related lookup and by second step you update your main table records, but, you can configure your import map to add new values to your lookups if their come.

Tuple may be good solution for your needs, but it not informative in Data manager

Regards

Kanstantsin Chernichenka

[Former Member]

Hi Kanstantsin,

For the moment, I've decided to create two tuples tables and link one of them to the other.

One tuple is 'Info records' and the other 'Source lists'.

Into the source list, the field 'Supplier' is defined as a tuple to 'info records' tuple table.

Then, the process will be:

1. Create the material number into the main table and Save it to get the auto id.

2. Also into the main table, fill in the field 'Source list' by informing:

               a. Material nr (lookup to main table)

               b. Supplier (tuple to info record tuple)

               c. Valid date: From / To (date fields)

               d. Fixed supplier (boolean field: X / false)

Into the 'Info record' tuple table I need to have also the 'Material number', as I can have different info records, depending on the supplier and the material. If I don't include it, it exists the possibility to assign a wrong info record (price) to the material. Also, I will not be able to recognize the material if I want to search directly into the info records qualified table.

Also, I need to have the 'Material number' into the Source list in order to know which is the valid supplier for each material.

What do you mean with 'it not informative in Data manager'???

Thanks!

Regards,

Carlos Santamaría.