How to create foreign keys from imported Excels as DBs?
Application Type
Mobile
Service Studio Version
11.50.11 (Build 47949)
Platform Version
11.13.0 (Build 31107)

I am facing several issues when I'm importing Excels:

Based on what I have experienced pretty much every error is related to how OutSystems is treating the Foreign Keys (or how I configured them). The first bug happened because OutSystems created extra Primary Keys related to the IDs I set in my Excel:

Fine, I cleaned them:

I removed the generic Id and set its values as autonumeric and Long Integer (Idea - Use default IDs from the Excels Columns when you import Excels | OutSystems)Then, I dragged and dropped the one to various symbols to build the relationships:

The issue is that those new foreign keys (ProductId2 and LocalUserId2) are not real relationships, they are new columns. How do I know it?

Dropdown values coming from a Form are not being stored in the database | OutSystems 

When I tried to save some data in that table, I got this view:

As you could see, the ProductId2 and LocalUserId2 columns have the new value but they are not reflecting the original values.

Now, are there any workarounds to fill those values? Perhaps, but I'm using OutSystems pretending that I have no idea about Software Development, I'm trying to use it in as no code as possible mode. Now, how can I create the correct relationships?

I want to highlight that the Excel I'm using is quite small and if the workaround is to fill the data manually and then delete the original columns then that sounds like a terrible business case. I don't imagine myself importing an Excel with 100,000 or 500,000 rows (I have had several of these ones over the years) by 40 columns and then edit the table manually. What would have been the purpose of using OutSystems in such a case? I can write a C# console app faster that will allow me to fill any table in an RDBMS reading the Excel and setting the data correctly.

Hopefully, there is a correct way to do the relationships, and I'm doing something wrong. Personally, I see this topic as a bug (or inexperience) and not exactly as a new feature because the "relationships" are fake.

Now, let's suppose a second case the one I told to Dorine in the first comment:

I cleared the Entity Diagram, deleted everything, re-imported everything, and added only the basic changes:

  • Removed the auto-created IDs.
  • Redefined the correct IDs
  • Changed the correct IDs properties based on the auto-created IDs, I set Is AutoNumber to Yes and Data Type as Long Integers.
  • Change the Data Type from LocalUserId and ProductId from Integer into Long Integers.

What should I modify in the ProductId and LocalUserId from the MyList table to make them work properly as potential Foreign Keys?

Normal view:

Advanced view:

In a normal tool for supporting RDBMS like phpMyAdmin, I'd expect a feature like this one:

It allows you to create relationships without major effort.

I explored the maximum that I could the tool, and I couldn't find any way to create the relationships manually. Maybe there is something that I'm missing.

Thanks for your time.

MySuperList.oml

For the foreign keys,

this is what you are not doing right

  • Change the Data Type from LocalUserId and ProductId from Integer into Long Integers.

you tell Outsystems something is a foreign key by its type being the identifier of the other table

So instead of changing them to Long Integers, change them to Product Identifier and LocalUser Identifier, you can find them in the data type dropdown under the basic types.

What you probably did, was drag the id from Product over to MyList, which is a fast way to create relationships that don't exist yet at all, but here the column was already defined.


Dorine

This is the correct answer. Clear and direct. Now, this is still a feature to be implemented -in my opinion-:

Idea - Accelerator should relate columns with the same names (IDs) between the Father Table and its Children Tables | OutSystems

Thanks.

Hi Federico,

So yes, defining your datamodel and loading data in it is not a trivial job, so it is to be expected that some engineering and decisions should go into it, more than just drag > drop > test.

So first off, the id's, Outsystems assumes the Id's are not there in the excel and creates it's own autonumber column for that, If you already have some sort of id system previously set up / available in your excel, you will need to amend that.  From your post I gather you chose to go with the id's from the excel, not the default ones outsystems generates, that's fine, but I see you are saying you put them on autonumber, that's not going to work, either they are autonumber, and you can't get the values from your excel, or you get the values from your excel and they can't be autonumber.  Make sure to have a look at the server action that will actually load your data when you make changes to the datamodel, to make sure it will load the data from excel as you would want.

Second, about the foreign keys, those columns are already there when you are importing the structure from excel (the ones without the 2 behind), so instead of dragging the id's from the referenced tables to create relationships (which will result in the columns with a 2 behind to avoid naming conflict) all you have to do is change the type of the 2 that are already there.  Outsytems doesn't try to assume for you which of the imported attributes might be foreign keys, so you'll have to tell it yourself.

I have no idea what you mean when you say they are not real relationships, they are !  Look at your image, they have a small black arrow in the icon.

Anyway, if you just change the definition of the 2 original attributes to be reference attributes to both product and userinfo, those 2 attributes with a 2 at the end will not need to be created, and all your other problems go away.

Actually, I don't think all your problems go away, because allthough Outsystems is a lowcode tool, you still need to understand how software is made, what datamodeling is about, how you put together an app, how all the parts are related to each other, what are the limitations of the available accelerators, and so on.  I suggest you take some of the very good beginner courses that are available on this website for free.  

Good luck,

Dorine

Hi Dorine, I don't think all problems will go away, but I'm trying to solve the problem in the current scenario with as few code as possible.

As I shared at the end of my question, I have considerable experience building apps that are deployed in multiple stores. Next, the relationships are fake starting that there is no data, OutSystems "guessed" correctly the right relationships but it didn't add any data to its new relations, on the contrary, it created unrequired new columns. I'd still wonder, why would you create new columns if your guess was correct?

Thinking as a coder:

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Source: SQL FOREIGN KEY Constraint (w3schools.com) 

When I define a foreign key in MySQL, the engine doesn't create a new column but it creates a relationship between the father table and the child table.

Let's continue with the original topic. I cleared the Entity Diagram, deleted everything, re-imported everything, and added only the basic changes:

  • Removed the auto-created IDs.
  • Redefined the correct IDs
  • Changed the correct IDs properties based on the auto-created IDs, I set Is AutoNumber to Yes and Data Type as Long Integers.
  • Change the Data Type from LocalUserId and ProductId from Integer into Long Integers.

What should I modify in the ProductId and LocalUserId from the MyList table to make them work properly as potential Foreign Keys?

Normal view:

Advanced view:

In a normal tool for supporting RDBMS like phpMyAdmin, I'd expect a feature like this one:

It allows you to create relationships without major effort.

I explored the maximum that I could the tool, and I couldn't find any way to create the relationships manually. Maybe there is something that I'm missing, and you could give me some hints.

Thanks for your time.

MySuperList.oml

mvp_badge
MVP

Hi Dorine, I up voted your reply first of all because what you write is a correct reply to the question, but also to show extra support as I have no clue why someone would down vote it.

Thanks for supporting but Dorine's last answer is the correct one, the one related to the identifiers. I couldn't find anything related to it in his first answer.

Read again :

"Anyway, if you just change the definition of the 2 original attributes to be reference attributes to both product and userinfo, those 2 attributes with a 2 at the end will not need to be created, and all your other problems go away."

Thanks, but the other answer still was clearer and more direct. It was strictly what I needed without any extra steps or extra interpretation.

For the foreign keys,

this is what you are not doing right

  • Change the Data Type from LocalUserId and ProductId from Integer into Long Integers.

you tell Outsystems something is a foreign key by its type being the identifier of the other table

So instead of changing them to Long Integers, change them to Product Identifier and LocalUser Identifier, you can find them in the data type dropdown under the basic types.

What you probably did, was drag the id from Product over to MyList, which is a fast way to create relationships that don't exist yet at all, but here the column was already defined.


Dorine

This is the correct answer. Clear and direct. Now, this is still a feature to be implemented -in my opinion-:

Idea - Accelerator should relate columns with the same names (IDs) between the Father Table and its Children Tables | OutSystems

Thanks.

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.