Solved

Look-up with more primary/foreign keys

  • 1 April 2020
  • 9 replies
  • 160 views

Userlevel 1
Badge +4

Hi, 

i have 2 tables as shown in the picture below:

 

when i try to set up a look-up in Subjects on item_cast columns, it works only for item_type_cast, the final output is shown in the picture below:

when i press the “search” icon, i see no entries in warehouse_items_table. 

Main problem is: i don’t know how to build a look-up with a reference to 2 primary keys.

icon

Best answer by Mark Jongeling 10 April 2020, 15:20

View original

9 replies

Userlevel 7
Badge +23

Hey Matteo,

For a PK to work with two or more columns the columns before the column you are trying to fill should be filled.

For instance, in your case, id/item_id_cast should be filled before you are able to select a value for item_type_cast. This has to do with the Lookup mechanics. To find a value to select, it needs all previous columns to have a value. Column 1 needs to be filled to be able to find a value for Column 2.

Does this suffice in helping you further? 

Kind regards and good luck!
Mark Jongeling

Userlevel 1
Badge +4

Hi,

my warehouse table already contain some data, but they are not displayed in the list.

If I try to add new data in the search form, i have an unkonwn warning shown below:

the warehouse record is insered correctly, but i cannot search it ad associate it to “cast_table” record. 

Userlevel 7
Badge +23

Hi Matteo,

To me it's unclear how your complete data model is looking like, but it looks like you should have an Item table where you select the item_id_cast and another table where you can select the item_type_cast from. Of course that is only if the combination of Item and Type can be different each time. If 1 item always has 1 type, then you could combine this information into one table.

I miss a bit too much information of the project you are working on to fully understand what you are going for. The PK problem you face is a logical problem and requires the solution given in my previous answer.

The problem you face now is a Lookup problem. That comes if the record you added is outside of the filtered range. I believe your filter includes the identity column, so an added record will always fall outside of the set.

Userlevel 1
Badge +4

Hi Mark!

The main problem is that when I alter a table in Thinkwise ( i modify foreign/primary keys, I add a new field), the table is not altered on MS SQL Server. I receive an error message that inform I cannot create tables because I don’t have permissions (the system wants to create them again but not alter).

Do I have to drop all the tables in MS SQL Server and create them again?

 

Userlevel 7
Badge +23

Hi Matteo,

That's strange. You are using the Creation in the Software Factory right? When trying to run the Upgrade script, with which account are you connected to the SQL database? The user that you use to connect to the SQL database should have sufficient permissions to create table. You can check in the SQL Server Management Studio (SSMS) which Server role your user has. 

With the last option you suggested, if preserving data is not necessary, you could drop the database and let the Software Factory create a new one for you. Else you could let the Software Factory create a new one and preserve your old database by renaming that database.

So to solve you main problem, you should have sufficient rights to create a table. Check your user by opening 'Logins’ in SSMS and check the Server roles of the user.

 

Hopes this helps you out!

Kind regards,
Mark Jongeling

Userlevel 1
Badge +4

I use the same credentials as in SQL management studio:

 

When I generate source code, i see no SQL update statements, but only create table statements. I’ve created also a “sa2” user with all rights as possible, but I have the same error.

Another example: if I create a new table from the scratch with Thinkwise, sql statement in “generate source code” is created, but on “generate source code” the create box isn’t flagged.

Maybe I’m doing some steps wrong.

Userlevel 7
Badge +23

I can sense you are quite new to the Software Factory (SF), which is okay :wink: I'll try to explain why the SF is responding this way:

There is a Model side (SF) and a Database side (SQL). Every project version there it is likely that there are changes in the data model. If ,for instance, you created a new table, the SF will create a table for you. But every time you make a change in the data model and created/upgraded the database, you should go to a new version of the project. The SF always looks back at the previous version and looks what has changed since. Those changes will be scripted and are ready to be executed. 

If after upgrading your database but not choose to go to the next project version, it will be likely that you run into problems. Because the SF thinks the database is at version 1.00 while in reality, changes already have been applied to the database; let's name this version 1.01. 

If you then continue to develop in the same version (1.00), the SF will still look at the previous version and gather all changes and makes scripts out of them. Those scripts include the new changes, but also the old changes you made. Those old changes can’t be applied twice. For instance, you can’t rename the same table twice from tableA to tableB, because the table is already called tableB. That way the script will fail.

The checkbox of 'Create’ isn't flagged because the SF senses that all tables have been created already, so it doesn’t flag the checkbox to prevent issues. 

I recommend using the E-learning environment to learn more about the Software Factory and how it works. There we have a great way to learn all the in's and out's of the Software Factory. I couldn't explain it better than the Software Factory E-learning course.

Hope this helps you further!

 

Userlevel 1
Badge +4

Thank you Mark, i’m using E-learning, but I missed that explanation. 

I thought I can alter tables without versioning the project. I’ll restart e-learning :)

Thank you

Userlevel 7
Badge +23

No problem Matteo, good luck!

If you need more help or have question, we are here at the Thinkwise Community :thumbsup_tone2:

Reply