What kind of reports are you trying to create?
Level#Name and Level#Number are redundant fields. I would normalize the data. If you absolutely cannot run the report you want from normalized data, you may be able to create a temporary table for the purpose of flattening out the data for your report.
i want to be able to import new data or updated data from SAP to FileMaker. So i think i need both, level number and level name if there are some organization changes in the company structure or group name. If i put those informations in the employee record, isn't it easier to produce reports?
I also want the users to be able to get reports from different levels without having to manipulate data.
How about a simple list, sorted by "StructuralGroupNumber" and others with Sub-summary area with Title of hierarchy levels?
Direction of Commercial accounts
i want to be able to import new data or updated data from SAP to FileMaker. So i think i need both, level number and level name if there are some organization changes in the company structure or group name.
Right - I'm not saying don't include level number and level name. I'm saying they belong in a different table in order to normalize the data. There may be reasons NOT to normalize the data, but as a default, I like to start with normalized data. So it might look something like:
Where LEVEL table has the fields LEVEL::number and LEVEL::name and 1000|Presidency, 2000|Vice President, etc. are records.
If i put those informations in the employee record, isn't it easier to produce reports?
It depends on the kind of report you want, but normalized data gives you the ability to pull reports as well.
The problem with your structure is that it can cause data anomalies. For example, in your spreadsheet above, if you deleted the records for Carl and Sophia you would lose all information on 3000|Direction of commercial accounts.
In this example you could create a calculated field that would take each of the numerical designation fields and rank them. Level1 x 1000 x 1000 + level 2 x 1000 + level 3 If this number becomes two large for FileMaker to sort then adjust the primary numbers accordingly. While this is good for sorting its not good for creating a hierarchical report. Why can't you just create a list break for each of the number categories? Use the number fields for a report category?